Ranking functions in sql Server

Ranking functions in SQL Server are used to give some rank (It could be unique or duplicate rank) to each record in a logical partition of result set. Below are the four ranking functions in SQL Server.

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • NTILE(n)

Test Data

To understand above Ranking functions we require some test Data, below is the SQL to generate test data. Refer below image to see the test data.

This test data contains Runs and Average of each player of IPL team Mumbai Indians against other teams.


-- Data Preperation for demo
DECLARE @Score TABLE (MIPlayer VARCHAR(50), AgainstTeam VARCHAR(50), IPLYear INT, Runs INT, Average INT)

INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Rohit Sharma', 'Delhi Daredavils', 2015, 78, 70)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Rohit Sharma', 'Gujrat Lions', 2015, 56, 60)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Rohit Sharma', 'Kings XI Punjab', 2015, 100, 120)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Rohit Sharma', 'Delhi Daredavils', 2016, 30, 77)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Rohit Sharma', 'Gujrat Lions', 2016, 67, 70)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Rohit Sharma', 'Kings XI Punjab', 2016, 25, 80)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Bumrah', 'Delhi Daredavils', 2015, 80, 70)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Bumrah', 'Gujrat Lions', 2015, 56, 70)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Bumrah', 'Kings XI Punjab', 2015, 90, 80)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Bumrah', 'Delhi Daredavils', 2016, 30, 100)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Bumrah', 'Gujrat Lions', 2016, 20, 100)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Bumrah', 'Kings XI Punjab', 2016, 25, 100)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Goplal', 'Delhi Daredavils', 2015, 80, 88)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Goplal', 'Gujrat Lions', 2015, 45, 88)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Goplal', 'Kings XI Punjab', 2015, 30, 90)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Goplal', 'Delhi Daredavils', 2016, 80, 70)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Goplal', 'Gujrat Lions', 2016, 20, 60)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('Goplal', 'Kings XI Punjab', 2016, 25, 100)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('McClenaghan', 'Delhi Daredavils', 2015, 70, 56)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('McClenaghan', 'Gujrat Lions', 2015, 10, 40)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('McClenaghan', 'Kings XI Punjab', 2015, 20, 30)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('McClenaghan', 'Delhi Daredavils', 2016, 80, 70)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('McClenaghan', 'Gujrat Lions', 2016, 80, 95)
INSERT INTO @Score (MIPlayer, AgainstTeam, IPLYear, Runs, Average) VALUES('McClenaghan', 'Kings XI Punjab', 2016, 100, 120)

--Query for ResultSet 0
SELECT * FROM @Score

TestData

Terminology

Below are the two terms which we should understand before jumping to Ranking functions.

  • Partition by clause – Column names under this clause are responsible to break query ResultSet into logical partitions.
  • Order by clause – Column names under this clause are responsible to decide ordered ranks.

ROW_NUMBER()

To understand ROW_NUMBER() function, refer below image, it demonstrates SQL, purpose and expected result in comparative formate.

ROW_NUMBER() generates unique ranks for each record of a partition irrespective of equality/inequality of column values.

ROWNUMBER1

ROWNUMBER2.PNG

RANK() and DENSE_RANK()

To understand RANK() and DENSE_RANK() functions, refer below image, it demonstrates SQL, purpose and expected result in comparative formate.

RANK() and DENSE_RANK() generate ranks for each record of partition, ranks of two records in a partition will be same if values of columns present in order by clause are same. In case of RANK(), few ranks will be skipped if duplicate ranks are assigned. In case of DENSE_RANK(), no ranks will be skipped (refer below images for clarity).

RANK.PNG

DENSE_RANK.PNG

NTILE(n)

To understand NTILE(n) function, refer below image, it demonstrates SQL, purpose and expected result in comparative formate.

NTILE(n) devides each partition in n parts, records in a part will have same rank. Number of records in parts of a partition could be equal or unequal, it depends upon if total number of records in a partition is divisible by n or not. Let us understand this.

Case 1 – No. of records in partion is 28 and we want to perform NTILE(5). Here 28 is not divisible by 5. Here next number to 28 which is divisible by 5 is 30 (i.e. 28 + 2).

30/5 = 6

In this case total number of parts in the partition will be 5 and each part of partition will have 6 records except the last part which will have (6 – 2 = 4) records. And rank of each record in 5 parts will be 1, 2, 3, 4, and 5 with respect to each part.

Case 2 – No of records in a partion is 42 and we want to perform NTILE(6). Here 42 is divisible by 6.

42/6 = 7

In this case total number of parts in the partition will be 6 and each part of partition will have 7 records. And Rank of each record in 6 parts will be 1, 2, 3, 4, 5, and 6 with respect to each part.

NTILE

Queries for Reference


--Query for ResultSet 1 -- Row_Number() Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
ROW_NUMBER() OVER(ORDER BY RUNS DESC) As [RANK] FROM @Score

--Query for ResultSet 2 -- Row_Number() Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
ROW_NUMBER() OVER(PARTITION BY AgainstTeam ORDER BY RUNS DESC) As [RANK] FROM @Score

--Query for ResultSet 3 -- Row_Number() Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
ROW_NUMBER() OVER(PARTITION BY AgainstTeam, IPLYear ORDER BY RUNS DESC) As [RANK] FROM @Score

--Query for ResultSet 4 -- Row_Number() Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
ROW_NUMBER() OVER(PARTITION BY AgainstTeam, IPLYear ORDER BY RUNS DESC, Average DESC) As [RANK] FROM @Score

--Query for ResultSet 5 -- RANK() Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
RANK() OVER(PARTITION BY AgainstTeam, IPLYear ORDER BY RUNS DESC, Average DESC) As [RANK] FROM @Score

--Query for ResultSet 6 -- DENSE_RANK() Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
DENSE_RANK() OVER(PARTITION BY AgainstTeam, IPLYear ORDER BY RUNS DESC, Average DESC) As [RANK] FROM @Score

--Query for ResultSet 7 -- NTILE(n) Example
SELECT MIPlayer, AgainstTeam, IPLYear, Runs, Average AS Avg,
NTILE(3) OVER(PARTITION BY AgainstTeam ORDER BY RUNS DESC) As [RANK] FROM @Score

2 thoughts on “Ranking functions in sql Server

Leave a Reply

Please log in using one of these methods to post your comment:

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