TransWikia.com

diffrent serial no for each ID in MSSQL

Stack Overflow Asked by Akash Singh on December 16, 2020

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

One Answer

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

Correct answer by Fahmi on December 16, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP