Ranking Functions

Today we are going to discuss about ranking functions and their benefits.Lets start with a basic question ,

• What is a Function ?

A function is a method which expects some parameters and will return an output.SQL Server has many functions.Since our point of interest for now is ranking function so we will discuss the same first.

• What is a Ranking Function ?

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows may receive the same value as other rows.SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are collectively called as ranking functions.. Ranking functions are non-deterministic.Non-deterministic as the name suggests cannot be determined(i.e output is not known to us).
Lets start with a simple example to make a better understanding.First i will create a table consisting some column and rows as follows:
CREATE TABLE tblSample (ID INT IDENTITY, Column1 INT, Column2 INT, Column3 INT)
CREATE UNIQUE CLUSTERED INDEX CLTDINDX ON tblSample(ID)
INSERT tblSample  VALUES (0, 1, 8)
INSERT tblSample  VALUES (0, 3, 6)
INSERT tblSample  VALUES (0, 5, 4)
INSERT tblSample  VALUES (0, 7, 2)
INSERT tblSample  VALUES (0, 9, 0)
INSERT tblSample  VALUES (1, 0, 9)
INSERT tblSample  VALUES (1, 2, 7)
INSERT tblSample  VALUES (1, 4, 5)
INSERT tblSample  VALUES (1, 6, 3)
INSERT tblSample  VALUES (1, 8, 1)
Now we are good to go.Lets start with a basic ROW_NUMBER Function:
SELECT *, ROW_NUMBER() OVER (ORDER BY Column2) AS RowNumber FROM tblSample
Above query will select all the rows from tblSample and will order Column2 and will assign sequential numbers (like sequence object or Identity) to each row.so the result will be:


From the above result you may see the column 2 has been ordered and the sequence number(in RowNumber column) has been assigned to each row.This will clear the definition of a ranking function i.e  Ranking functions returns a ranking value for each row.
Lets take another scenario where you want to group the rows in a particular format.If you closely look at the values in column 1 you will see column 1 has two numbers 0 and 1.Now you want to group the sequence numbers according to column 1.Here we will use partition on column 1.see below query :
SELECT *, ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2) AS RowNumber FROM tblSample
Above query will partition or you can say group the column1 into two parts one containing 0 and other as 1.output will be :

Above result,all the 0 of column1 has different set of sequence numbers and 1 of coulm1 has different set of sequence.
It is possible to combine multiple ROW_NUMBER functions (or multiple of any of the other ranking functions) with different ORDER BY clauses in a single query:
SELECT *, ROW_NUMBER() OVER (ORDER BY Column2) AS RowNumber1,ROW_NUMBER() OVER (ORDER BY Column3) AS RowNumber2 FROM tblSample
That was all about ROW_NUMBER().Lets see how other function behaves.next Function we are going to discuss is about RANK() & DENSE_RANK().Both the functions are pretty much same.the tables and values are going to be same for better understanding.
lets get back to the previous query we used for rownumber to get a better understanding about rank and dense rank.
SELECT *, ROW_NUMBER() OVER (ORDER BY Column2) AS RWNUM,RANK() OVER (ORDER BY Column2) AS Rank,
DENSE_RANK() OVER (ORDER BY Column2) AS DRank FROM tblSample
Output of this query will be:

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.