I would like to create a "count" or "order" field that would auto-increment, but restart after every change in a different field? In the table below, the "Order" field would restart at "1" every time there was a change in the "Meal" field. Thanks.

I tried this:

SELECT
    t1.meal,
    COUNT(t2.meal) + 1 AS RowNumb
FROM CTE t1
JOIN CTE t2 ON t2.time < t1.time OR (t2.time = t1.time AND t2.meal <> t1.meal)
GROUP BY
    t1.time,
    t1.meal
ORDER BY
    t1.time;

But I am getting this:

>Solution :

Use a subquery to count the rows for the same meal and a time till then:

SELECT
  t.meal,
  t.time,
  (
    SELECT COUNT(*)
    FROM mytable t2
    WHERE t2.meal = t.meal
    AND t2.time <= t.time
  ) AS rownumb
FROM mytable t
ORDER BY t.meal, t.time;