Search code examples
sqlcountauto-incrementdatabase-partitioning

Auto-increment field that resets after a change in another field

Can you provide a very simple SQL example of how 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    Order
Lunch    10:30     1
Lunch    11:00     2
Lunch    11:15     3
Dinner    4:30      1
Dinner    4:45      2
Dinner    5:00      3
Dinner    5:30      4

Solution

  • Instead of storing Order in the table, consider adding it to a view. You can select from the view instead of the table when you need it.

    The view could use row_number() to calculate the order, like:

    select  row_number() over (partition by Meal order by Time)
,       *
from    YourTable

    Example at SE Data.