With examples, this article will teach you how to use ranking functions in SQL Server. The ranking function is one of SQL Server's window functions. This gives each row a rank depending on the specified column. We have the ranking functions shown below.

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('Peter','english',90)
INSERT INTO StudentsReport VALUES('John','english',98)
INSERT INTO StudentsReport VALUES('Mark','english',98)
INSERT INTO StudentsReport VALUES('Alex','english',100)
INSERT INTO StudentsReport VALUES('Leo','english',86)
INSERT INTO StudentsReport VALUES('Maria','english',91)
INSERT INTO StudentsReport VALUES('Scott','english',86)
INSERT INTO StudentsReport VALUES('Laura','english',98)

SELECT * FROM StudentsReport;

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;

We can observe in the preceding result set that some of the rows have the same RANKnumber because they have the same value in the Marks column. For example, it assigned the same RANKnumber, 1 for identical marks, 86, but skipped the following RANKnumber 2 and went straight to RANKnumber 3.

Because we did not apply the PARTITION BY clause, the above result is displayed in a single partition.

Let's look at another example where we'll use the 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;

On the above result set, we can see that it assigns unique Rownumbers in sequential order and sorts the results based on column Marks. Because we did not apply the PARTITION BY clause, the above result is displayed in a single partition. 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;

We can see in the above result set that it divides rows into a number of groups defined by the function.
Partitioning by clause works the same for all four-ranking functions.

Conclusion
We learned about the ranking functions available in SQL Server and how to apply them in this post. I hope you found this post helpful; please share your thoughts and suggestions in the comments area.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.