Using The RANK() Function

Hey everyone! Welcome to (or back to) SQL Something!

Quick filler for this month: Using The RANK() Function.

I will demonstrate this function using a very simplistic example.

Firstly let's look at a table I created called [Files] that consists of the below dummy data:

Fig. 1: Important data...

Now let's say I wanted to know what was the order of the data by Date in descending order. I can use a simple "...ORDER BY [Date] DESC" and get the job done.

Fig. 2: The data is ordered by date.

Now say I wanted a column that designates the above order with a ranking system, i.e., a column that shows which date is 'first', 'second', etc. in descending order. That's where RANK() can come in handy.

SELECT [File_ID]
      ,[Tran_ID]
      ,[Message]
      ,[Date]
      , RANK() OVER (ORDER BY [Date] DESC) AS DateRank
  FROM [Files]

The RANK function is used in the SELECT clause to add this column. We specify what column we wish to use to rank data by (in this case, 'Date') and in what order (in this case, descending). We can then apply an alias.

If you're like me, you might initially have been weirded out seeing "ORDER BY" in the SELECT clause. :-p

The above query yields the below results:

Fig. 3: Rank it up.

Now let's say we wish to go one step further. Let's say we wish to group the data and perform rankings within those groups/partitions. RANK() allows for this.

SELECT [File_ID]
      ,[Tran_ID]
      ,[Message]
      ,[Date]
      , RANK() OVER (PARTITION BY [Tran_ID] ORDER BY [Date] DESC) AS DateRank
  FROM [Files]

Here we have added the "PARTITION BY" clause to the RANK() function. This will partition the results by Tran_ID and THEN perform the ranking within those partitions. The above query will yield the below results:

Fig. 4: Rankings within partitions.

As we can see, Tran_ID was grouped into sets and in the DateRank field we can see how the dates were ranked within these sets/partitions.


Wow, I really did not expect to write this much or put any pictures. Honestly I was just going to post a couple lines and some T-SQL. :-p

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)