CODEWITHSUNDEEP
sqlsql-servert-sql
Akash Singh
Akash Singh

Reputation: 53

diffrent serial no for each ID in MSSQL

I have a table where I Have 2 ids table is like

ID    Date                       Shift(hr)   serial no 
54     11/10/2020                 2           1
54     10/10/2020                 5           2 
53     11/10/2020                 4           3
53     10/10/2020                 5           4

I want Output as below table is like

ID    Date                       Shift(hr)   serial no 
54     11/10/2020                 2           1
54     10/10/2020                 5           2 
54     09/10/2020                 6           3
53     11/10/2020                 4           1
53     10/10/2020                 5           2

Like based on date serial no should come for each ID numbering should start from the latest date Kindly Suggest something thanks

Upvotes: 0

Views: 45

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try using row_number()

select id, date, Shift, 
       row_number() over(partition by id order by date desc) as serial_no
from tablename

Upvotes: 2

Related Questions