TSQL : Ranking records according to the latest date

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;

Leave a comment