Search code examples
mysqlsqlsql-updateauto-increment

How create new col by increment it based on MIN(DATE) in SQL

i would like to create new column based on date. If it's the MIN(DATE) then new_col = 0 else new_col = 1 or 2 or 3 etc.

So i've this table :

customer_id date
1 2020-01-01
2 2020-12-01
2 2020-03-02
3 2020-04-06
1 2020-07-06
1 2020-08-12

and i want this output :

customer_id MIN(date) date new_col
1 2020-01-01 2020-01-01 0
2 2020-03-02 2020-12-01 0
2 2020-03-02 2020-03-02 1
3 2020-04-06 2020-04-06 0
1 2020-01-01 2020-07-06 1
1 2020-01-01 2020-08-12 2

So i would like to increment this new column by 1 for each date group by MIN(DATE).

Thanks by advance for your help

Solution

  • you can use window function:

    select * , row_number() over (partition by customer_id order by date) -1 new_col
from table