One frequently occurring scenario I come across is a bunch of records with the same identifiers but each row has a different datestamp – then I need to identify the latest record in each group.
This is the end result of what needs to be achieved, I want a ranking for each record in the group, the newest date will be ranked 1, then increment up to the oldest date, for example :
Id | UpdateDate | Rank |
1 | 2013-04-11 | 3 |
1 | 2013-04-12 | 2 |
1 | 2013-04-13 | 1 |
And this is the code to do it :
SELECT *, row_number() over ( partition by Id order by UpdateDate desc) as [Rank] FROM dbo.SomeTable;