Overview of SQL RANK functions

We perform calculations on data using various aggregated functions such as Max, Min, and
AVG. We get a single output row using these functions. SQL Sever provides SQL RANK
functions to specify rank for individual fields as per the categorizations. It returns an
aggregated value for each participating row. SQL RANK functions also knows as Window
Functions.

 Note: Windows term in this does not relate to the Microsoft Windows operating
system. These are SQL RANK functions.

We have the following rank functions.

 ROW_NUMBER()
 RANK()
 DENSE_RANK()
 NTILE()

In the SQL RANK functions, we use the OVER() clause to define a set of rows in the result set.
We can also use SQL PARTITION BY clause to define a subset of data in a partition. You can
also use Order by clause to sort the results in a descending or ascending order.

We have the following sample data in the ExamResult table.

Let’s use each SQL Rank Functions in upcoming examples.

ROW_Number() SQL RANK function

We use ROW_Number() SQL RANK function to get a unique sequential number for each row in
the specified data. It gives the rank one for the first row and then increments the value by one
for each row. We get different ranks for the row having similar values as well.

Execute the following query to get a rank for students as per their marks.

1 SELECT Studentname,
2 Subject,
3 Marks,
4 ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
5 FROM ExamResult;
By default, it sorts the data in ascending order and starts assigning ranks for each row. In the
above screenshot, we get ROW number 1 for marks 50.

We can specify descending order with Order By clause, and it changes the RANK accordingly.

1 SELECT Studentname,
2 Subject,
3 Marks,
4 ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumber
5 FROM ExamResult;

RANK() SQL RANK Function

We use RANK() SQL Rank function to specify rank for each row in the result set. We have
student results for three subjects. We want to rank the result of students as per their marks in
the subjects. For example, in the following screenshot, student Isabella got the highest marks
in English subject and lowest marks in Maths subject. As per the marks, Isabella gets the first
rank in English and 3rd place in Maths subject.
Execute the following query to get this result set. In this query, you can note the following
things:

 We use PARTITION BY Studentname clause to perform calculations on each student

group
 Each subset should get rank as per their Marks in descending order
 The result set uses Order By clause to sort results on Studentname and their rank

1 SELECT Studentname,
2 Subject,
3 Marks,
4 RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Ran
5 k
6 FROM ExamResult
7 ORDER BY Studentname,
Rank;

Let’s execute the following query of SQL Rank function and look at the result set. In this query,
we did not specify SQL PARTITION By clause to divide the data into a smaller subset. We use
SQL Rank function with over clause on Marks clause ( in descending order) to get ranks for
respective rows.

1 SELECT Studentname,
2 Subject,
3 Marks,
4 RANK() OVER(ORDER BY Marks DESC) Rank
5 FROM ExamResult
6 ORDER BY Rank;

In the output, we can see each student get rank as per their marks irrespective of the specific
subject. For example, the highest and lowest marks in the complete result set are 90 and 50
respectively. In the result set, the highest mark gets RANK 1, and the lowest mark gets RANK
9.

If two students get the same marks (in our example, ROW numbers 4 and 5), their ranks are
also the same.
DENSE_RANK() SQL RANK function

We use DENSE_RANK() function to specify a unique rank number within the partition as per the
specified column value. It is similar to the Rank function with a small difference.

In the SQL RANK function DENSE_RANK(), if we have duplicate values, SQL assigns different
ranks to those rows as well. Ideally, we should get the same rank for duplicate or similar
values.

Let’s execute the following query with the DENSE_RANK() function.

1 SELECT Studentname,
2 Subject,
3 Marks,
4 DENSE_RANK() OVER(ORDER BY Marks DESC) Ran
5 k
6 FROM ExamResult
ORDER BY Rank;

In the output, you can see we have the same rank for both Lily and Isabella who scored 70
marks.

Let’s use DENSE_RANK function in combination with the SQL PARTITION BY clause.

1 SELECT Studentname,
2 Subject,
3 Marks,
4 DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Marks DESC) Ran
 5 k
6 FROM ExamResult
7 ORDER BY Studentname,
Rank;

We do not have two students with similar marks; therefore result set similar to RANK Function
in this case.

Let’s update the student mark with the following query and rerun the query.

1 Update Examresult set Marks=70 where Studentname='Isabella' and Subject='Maths'

We can see that in the student group, Isabella got similar marks in Maths and Science subjects.
Rank is also the same for both subjects in this case.

Let’s see the difference between RANK() and DENSE_RANK() SQL Rank function with the
following query.

 Query 1

1 SELECT Studentname,
2 Subject,
3 Marks,
4 RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) Ran
5 k
6 FROM ExamResult
7 ORDER BY Studentname,
 Rank;

 Query 2

1 SELECT Studentname,
2 Subject,
3 Marks,
4 DENSE_RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) Ran
5 k
6 FROM ExamResult
7 ORDER BY Studentname,
Rank;

In the output, you can see a gap in the rank function output within a partition. We do not have
any gap in the DENSE_RANK function.

In the following screenshot, you can see that Isabella has similar numbers in the two subjects.
A rank function assigns rank 1 for similar values however, internally ignores rank two, and the
next row gets rank three.

In the Dense_Rank function, it maintains the rank and does not give any gap for the values.

NTILE(N) SQL RANK function

We use the NTILE(N) function to distribute the number of rows in the specified (N) number of
groups. Each row group gets its rank as per the specified condition. We need to specify the
value for the desired number of groups.

In my example, we have nine records in the ExamResult table. The NTILE(2) shows that we
require a group of two records in the result.

1 SELECT *,
2 NTILE(2) OVER(
3 ORDER BY Marks DESC) Rank
 4 FROM ExamResult
5 ORDER BY rank;

In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four
rows.

Similarly, NTILE(3) divides the number of rows of three groups having three records in each
group.

1 SELECT *,
2 NTILE(3) OVER(
3 ORDER BY Marks DESC) Rank
4 FROM ExamResult
5 ORDER BY rank;

We can use SQL PARTITION BY clause to have more than one partition. In the following query,
each partition on subjects is divided into two groups.

1 SELECT *,
2 NTILE(2) OVER(PARTITION BY subject ORDER BY Marks DESC) Ran
3 k
4 FROM ExamResult
ORDER BY subject, rank;
Practical usage of SQL RANK functions

We can use SQL RANK function to fetch specific rows from the data. Suppose we want to get
the data of the students from ranks 1 to 3. In the following query, we use common table
expressions(CTE) to get data using ROW_NUMBER() function and later filtered the result from
CTE to satisfy our condition.

1 WITH StudentRanks AS
2 (
3 SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks
4 FROM ExamResult
5 )
6 SELECT StudentName , Marks
7 FROM StudentRanks
8 WHERE Ranks >= 1 and Ranks <=3
9 ORDER BY Ranks
1
0

We can use the OFFSET FETCH command starting from SQL Server 2012 to fetch a specific
number of records.

1 WITH StudentRanks AS
2 (
3 SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks
4 FROM ExamResult
5 )
6 SELECT StudentName , Marks
7 FROM StudentRanks
8 ORDER BY Ranks OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;
9
A quick summary of SQL RANK Functions

ROW_Numbe It assigns the sequential rank number to each unique record.

r

RANK It assigns the rank number to each row in a partition. It skips the number for similar values.

Dense_RANK It assigns the rank number to each row in a partition. It does not skip the number for similar
values.

NTILE(N) It divides the number of rows as per specified partition and assigns unique value in the partition.