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;

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s