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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s