Rank Records According to Date with RANK() TSQL

You have a bunch of active records that you would like to rank in descending order of record date i.e. most recent gets the ranking 1, etc.

A quick sample is provided here to get you going.  This will select your records but add an additional column called RecordRank to store the Rank value.


SELECT RecordKey, RecordStatus, RecordDate,
 RANK() OVER (PARTITION BY RecordKey ORDER BY RecordDate DESC) AS RecordRank
 FROM dbo.Records

A full working example is provided below with sample data.


CREATE TABLE dbo.Records
(
RecordKey int,
RecordStatus varchar(10),
RecordDate datetime
)
GO

INSERT dbo.Records
VALUES
(1,'Active','2010-04-01 15:37:00.000')
INSERT dbo.Records
VALUES
(1,'Active','2010-04-03 11:05:00.000')
INSERT dbo.Records
VALUES
(1,'Active','2010-05-01 09:00:00.000')
GO
SELECT RecordKey, RecordStatus, RecordDate,
RANK() OVER (PARTITION BY RecordKey ORDER BY RecordDate DESC) AS RecordRank
FROM dbo.Records

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