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

**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.

**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).

**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.

**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

The Best Explanation I ever see, tnks Sir.

LikeLike

Your comment is great encouragement.Thanks!

LikeLike