Ranking Function in SQL Server

In this article you will learn how to use ranking functions in SQL Server with examples.

Introduction

Ranking function is one of the window functions in SQL Server. This assigns a rank to each row based on the given column. We have the following ranking functions.

Let’s learn each ranking function in detail.

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

Firstly, we will create the table StudentsReport to understand each function.

CREATE TABLE StudentsReport
(
StudentName VARCHAR(50),
Subjects VARCHAR(50),
Marks INT
)

INSERT INTO StudentsReport VALUES('Raju','english',90)
INSERT INTO StudentsReport VALUES('Ajay','english',98)
INSERT INTO StudentsReport VALUES('Mani','english',98)
INSERT INTO StudentsReport VALUES('Asha','english',100)
INSERT INTO StudentsReport VALUES('Neeti','english',86)
INSERT INTO StudentsReport VALUES('Rekha','english',91)
INSERT INTO StudentsReport VALUES('Raj','english',86)
INSERT INTO StudentsReport VALUES('Seema','english',98)

SELECT * FROM StudentsReport;

And table StudentsReport look like below.

Now let’s start with very first RANK () function to see how it works in SQL server.

RANK () function

RANK () function is a window function which assign a rank to each row within its partition. Now let’s see the syntax of rank () function and understand how we use it in SQL Server.

SELECT Column_Name
RANK() OVER (PARTITION BY Expression ORDER BY Expression [ASC/DESC]) AS Rank
FROM Table_Name;

IN THIS SYNTAX, OVER clause sets the partitioning and ordering of the results before applying the corresponding window function.

PARTITION BY clause divides the rows of the result set into partitions to which the function is applied.

ORDER BY clause is required to set the rows in ascending or descending order in each partition where the function is applied.

Now let’s understand the rank () function with example.

SELECT StudentName, Subjects, Marks, 
RANK() OVER(ORDER BY marks) AS RANKnumber 
FROM StudentsReport;

In the above result set we can see that some of the rows have same RANKnumber as they have equal value in Marks column. For example, it assigned the same RANKnumber i.e. 1 for equal marks i.e. 86 however it skipped the next RANKnumber 2 and directly jumped to RANKnumber 3.

The above result is showing in single partition as we are not using PARTITION BY clause.

Now let’s take another example where we will use PARTITION BY clause.

SELECT StudentName, Subjects, Marks, 
RANK() OVER(PARTITION BY Marks ORDER BY StudentName) AS RANKnumber 
FROM StudentsReport;

In above result set we can see PARTITION BY clause divides the rows based on column Marks and assign the RANKnumber to each row within its partition and used order by clause in column StudentName to sort the result.

ROW_NUMBER () Function

This function returns a unique Rownumber in sequential order for each row in the result. Below is the Syntax of ROW_NUMBER () function.

SELECT Column_Name
ROW_NUMBER() OVER (PARTITION BY Expression ORDER BY Expression [ASC/DESC]) AS Rownumber
FROM Table_Name;

Now let’s understand the ROW_NUMBER () function with example to see how it works.

SELECT StudentName, Subjects, Marks, 
ROW_NUMBER() OVER(ORDER BY Marks) Rownumber
FROM  StudentsReport;

We can see on the above result set, it assigns unique Rownumber in sequential order and sorting the result based on column Marks. The above result is showing in single partition as we are not using PARTITION BY clause.

Now let take another example where we will use PARTITION BY clause.

SELECT StudentName, Subjects, Marks, 
ROW_NUMBER() OVER(PARTITION BY Marks ORDER BY StudentName) Rownumber
FROM  StudentsReport;

In above result set we can see PARTITION BY clause works in ROW_NUMBER () function same as RANK () function partition and we can see the same result set.

DENSE_RANK () function

This function returns consecutive rank for column defined in the function. Below is the Syntax of DENSE_RANK () function.

SELECT Column_Name
DENSE_RANK() OVER (PARTITION BY Expression ORDER BY Expression [ASC/DESC]) AS Denserank
FROM Table_Name;

Now let’s understand the DENSE_RANK () function with example to see how it works.

SELECT StudentName, Subjects, Marks, 
DENSE_RANK() OVER(ORDER BY Marks) Denserank 
FROM  StudentsReport;

In the above result set we can see that some of the rows have same denserank as they have same value in Marks column. for example, it assigned the same denserank i.e. 1 for same marks and then jumped to denserank 2 for another marks.

NTile(N) Function

NTile(N) Function is a window function that distributes rows in a number of groups defined in the function. for example, in NTILE(N) function if we take N=4 and it will distribute result set in 4 groups. Below is the Syntax of NTILE(N) function.

SELECT Column_Name    
NTILE(N) OVER (PARTITION BY Expression ORDER BY Expression [ASC/DESC]) AS NTileOutput
FROM Table_Name;  

Now let’s understand the NTILE(N) function with example to see how it works.

SELECT StudentName, Subjects, Marks, 
NTILE(4) OVER (ORDER BY marks DESC) AS NTileOutput 
FROM StudentsReport;

In the above result set we can see that it distributes rows in a number of groups defined in the function.

Note. Partition by clause works same for all 4-ranking function.

Conclusion

In this article, we have learnt ranking functions available in SQL Server and how to use them. Hope you found this article useful, please share your feedback\suggestions in comments section.