Post
Ask Question

The Complete Reference - Ranking Functions In MS SQL - ROW_NUMBER(), RANK(), DENSE_RANK() And NTILE()

Article Overview

  • Background
  • Prerequisites
  • Ranking Functions - ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()
  • Complete Example
  • Practical Scenario
  • Summary

Background

 
There are various situations where you need to perform ranking on the result data set.
 
For example, you have Members data and you want to display Sr Number with member’s name. For this, you can use Row_Number().
 
Let us consider anothe. There are members having points and based on points you want to give rank to them. This can be achieved easily using Rank() function.
 
To perform such kind of various ranking operations on the result data set; SQL provides mainly the following four functions,
  1. ROW_NUMBER()
  2. RANK(),
  3. DENSE_RANK()
  4. NTILE()
Prerequisites
  • You should have a basic knowledge of MS SQL and queries.

Ranking Functions - ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

 
Now, let us understand each function one by one with practical example.
 
Before beginning, let us create one Member table and insert some data into it with the following script,
 
Create table
  1. CREATE TABLE Member  
  2. (  
  3.   Id INT PRIMARY KEY,  
  4.   Name VARCHAR(25),  
  5.   Point INT  
  6. )  
 Insert data into the table
  1. INSERT INTO Member VALUES (1,'Sachin', 978)  
  2. INSERT INTO Member VALUES (2,'Rahul', 773)  
  3. INSERT INTO Member VALUES (3,'Kamplesh', 1141)  
  4. INSERT INTO Member VALUES (4,'Chirag', 773)  
  5. INSERT INTO Member VALUES (5,'Pratik', 1242)  
  6. INSERT INTO Member VALUES (6,'Rajesh', 1141)  
  7. INSERT INTO Member VALUES (7,'Anil', 886)  

ROW_NUMBER()

 
It is used to return a unique sequential number for the each row within the specified partition.
 
It starts from 1 for the first row in each partition and without repeating or skipping numbers in the ranking result of each partition. For duplicate values within the row set, the ranking ID numbers will be assigned arbitrarily. This scenario is covered in Example-2.
 
If PARTITION BY clause is specified, the ranking row number will be reset for each partition. This scenario is covered in Example-3.
 
Generally, ROW_NUMBER() is used to provide serial number to the records as mentioned in Example-1.
 
Syntax
 
ROW_NUMBER () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
 
Example 1
 
How to display member names in alphabet order with Serial Number like 1, 2, 3, etc.? 
  1. SELECT ROW_NUMBER() OVER( ORDER BY Name) AS SrNo, Name FROM Member  
Output
 
 
As you can see here, the default order is ascending order.
 
Example 2
 
How to give ranks to the members based on their point and display it in top rank to least rank? 
  1. SELECT ROW_NUMBER() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member  
Output
 
 
Partition By clause
 
You can use partition by clause with it to divide result with some specified partition.
 
Example 3
 
How to give ranks to point for the members based on their point and display it in top rank to least rank? 
  1. SELECT ROW_NUMBER() OVER( PARTITION BY Point ORDER BY Point desc) AS [Rank]
  2. , Name, Point FROM Member  
Output
 
 

RANK()

 
It is used to return a unique rank number for the each distinct row within the specified partition.
 
It starts from 1 for the first row in each partition, with the same rank for duplicate values and it leaves gaps between the ranks; this gap appears in the sequence after the duplicate values.
 
RANK() behaves like ROW_NUMBER() function except for the rows with equal values, where it will rank with the same rank ID and generate a gap after it.
 
Generally, RANK() is used to provide ranking to the records as mentioned in Example-4.
 
Syntax
 
RANK () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
 
Example 4
 
How to display member names in rank given by their points? For example highest point will come first and lowest last? 
  1. SELECT RANK() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member  
Output
 
 
As you can see here, the default order is ascending order.
 
Example 5
 
How to display members who are having second highest? 
  1. select * from (  
  2. SELECT RANK() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member  
  3. ) as tmp where tmp.[Rank] = 2  
Output
 
 
Note that, generally, this question is asked in interview question such as how to get list of second highest salary employees.
 
Partition By clause: You can also use partition by clause with it to divide results with some specified partition.
 

DENSE_RANK()

 
It is similar to RANK() but the only difference is DENSE_RANK() does not skip any rank, i.e. leaving no gap(s) between the gap(s).
 
Generally, DENSE_RANK() is used to provide ranking to the records as mentioned in Example-6.
 
Syntax
 
DENSE_RANK () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
 
Example 6
 
How to display member names in rank given by their points. For example highest point will come first and lowest last? 
  1. SELECT DENSE_RANK() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member  
Output
 
 
As you can see here, the default order is ascending order.
 
Example 7
 
How to display third highest point? 
  1. select Point from (  
  2. SELECT DENSE_RANK() OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member  
  3. ) as tmp where tmp.[Rank] = 3  
Output
 
 
Note that, generally, this question is asked in interview question such as how to get second highest salary.
 
Partition By clause
 
You can also use partition by clause with it to divide result with some specified partition.
 

NTILE()

 
It is used to distribute the rows in to the rows set with a specific number of groups.
 
It provides each row in the result set with a unique group number. It starts from 1 that shows the group that belongs to this group. N is a positive number and it distributes the rows set into.
 
Generally, NTILE () is used to divide the records into the specific number of in Example-8.
 
Syntax
 
NTILE(buckets) OVER ([PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ...)
 
Example 8
 
How to divide the members in 3 groups by their points? For example so that the highest point will come first and the lowest last into the group? 
  1. SELECT NTILE(3) OVER( ORDER BY Point desc) AS [Rank], Name, Point FROM Member  
Output
 
 

Complete Example - Difference between ROW_NUMBER(), RANK(), DENSE_RANK()

 
Now, let us see all these three together to know the actual difference.
 
Example 9
 
How to display actual difference between ROW_NUMBER(), RANK(), DENSE_RANK()? 
  1. SELECT Name, Point  
  2. , ROW_NUMBER() OVER(ORDER BY Point desc) AS [ROW_NUMBER]  
  3. , RANK() OVER( ORDER BY Point desc) AS [RANK]  
  4. , DENSE_RANK() OVER( ORDER BY Point desc) AS [DENSE_RANK]  
  5. FROM Member  
Output
 
 
Here, you can see that ROW_NUMBER returns a unique number for each row. Rank returns duplication as well as there is gap of 3 while in case of DENSE_RANK there is no gap in between unique numbers. 
 

Practical Example/Scenario

 
Now, let us take two examples of real life use of such functions.
 
Example 10
 
How to display the list of members whose rank is between 2 and 4? 
  1. WITH MemberRanks AS  
  2. (  
  3.    SELECT Name, Point  
  4.    , DENSE_RANK() OVER( ORDER BY Point desc) AS [Rank]  
  5.    FROM Member  
  6. )  
  7. SELECT Name , Point , [Rank]  
  8. FROM MemberRanks  
  9. WHERE [Rank] >= 2 and [Rank] <=4  
  10. ORDER BY [Rank]  
Output
 
 
Here, the only those members will be displayed whose rank is in between 2 and 4 by their points. 
 
Example 11
 
How to display the list of members by page by their name? Suppose there are 100 records and my page size is 25 and I have given page number 2 than it should display 26 to 50 records? 
  1. WITH Members AS  
  2. (  
  3.    SELECT Name, ROW_NUMBER() OVER( ORDER BY Name) AS RowNo  
  4.    FROM Member  
  5. )  
  6. SELECT Name , RowNo  
  7. FROM Members  
  8. ORDER BY  
  9. RowNo OFFSET 26 ROWS FETCH NEXT 50 ROWS ONLY;  
Output
 
It will display 25 records from 26 to 50 according to serial number by their name. But, here we do not have that much data so it will not display.
 

Summary

 
There are 4 ranking functions ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() are in MS SQL. These are used to perform some ranking operation on result data set.
 
  • ROW_NUMBER() gives unique sequential numbers for each row.
  • RANK()returns a unique rank number for each distinct row. It behaves like ROW_NUMBER() except for the rows with equal values, where it will rank with the same rank ID and generate a gap after it.
  • DENSE_RANK() is similar to RANK() but the only difference is DENSE_RANK() does not skip any rank, i.e. leaving no gap(s) between the gap(s).
  • NTILE() is used to distribute the rows in to the rows set with a specific number of groups.
Here, I have described each one with real practical examples and also given the difference between theme in the last section.
 
Now, I believe you will be able to know the key important things about ranking functions ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() are in MS SQL.


Similar Articles