0

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.

Meal Time RowNumb
Lunch 10:30 1
Lunch 11:00 2
Lunch 11:30 3
Dinner 4:30 1
Dinner 5:00 2
Dinner 5:30 3
Dinner 6:00 4

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:

Meal Time RowNumb
Lunch 10:30 1
Lunch 11:00 2
Lunch 11:30 3
Dinner 4:30 4
Dinner 5:00 5
Dinner 5:30 6
Dinner 6:00 7
NickW
  • 8,430
  • 2
  • 6
  • 19
luanstat
  • 65
  • 1
  • 9
  • Please tag your question with the DBMS you are using - as the answer may be product-specific. Why don't you want to use ROW_NUMBER or PARTITION BY? – NickW May 25 '23 at 11:24
  • You select 2 columns, but your result has 3 columns... – jarlh May 25 '23 at 11:30

1 Answers1

2

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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73