DENSE_RANK SQL Server Function for Nth Highest Record

Problem

We can easily use the MAX() function in SQL Server to find the maximum value in a table. However, there are situations when the second-highest or third-highest record is needed from the table. SQL Server has no direct SQL functions to fetch the second or third highest from the table, so how can I accomplish this task?

Solution

This SQL tutorial will show how the SQL Server window function DENSE_RANK() can be leveraged to get the nth highest record from a table.

The SQL Server DENSE_RANK() function attaches a rank with each row inside the result set partition. The DENSE_RANK() method, in contrast to the RANK() method, returns a series of rank values. If two rows in each partition have the same values, they are ranked similarly.

To understand the difference between the RANK() and DENSE_RANK() methods, check out the following tip: SQL Server Window Functions RANK, DENSE_RANK and NTILE.

DENSE_RANK Function Syntax

DENSE_RANK() OVER (
    [PARTITION BY expressionForPartition]
    ORDER BY expressionForSorting
)

The PARTITION BY clause defines the partition within the table, whereas the ORDER BY clause defines the order as either ascending (ASC) or descending (DESC). The PARTITION BY clause is optional; if the PARTITION BY clause is omitted, it considers all the datasets as one partition. Default sorting in the ORDER BY clause is ascending (ASC).

Create Schema for Testing

The following syntax will be used to create the table and insert values:

--create table syntax
CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type,
    column_name3 data_type,
   ....
);
 
--insert values in the table syntax
INSERT INTO table_name VALUES (column_value1, column_value2, column_value3, ...);

For this tutorial, two tables will be created. The following statements are the names and scripts of the two demo tables.

Employee – (id, name, department, salary)

CREATE TABLE Employee (
   [id] int identity(1,1) not null Primary Key,
   [name] varchar(100),
   [department] varchar(100),
   [salary] int
);
--not providing id value since its auto generated
INSERT INTO Employee(name, department, salary) VALUES ('Employee1','Development',5500);
INSERT INTO Employee(name, department, salary) VALUES ('Employee2','Development',5000);
INSERT INTO Employee(name, department, salary) VALUES ('Employee3','Development',5700);
INSERT INTO Employee(name, department, salary) VALUES ('Employee4','Development',6500);
INSERT INTO Employee(name, department, salary) VALUES ('Employee5','Development',6700);
INSERT INTO Employee(name, department, salary) VALUES ('Employee6','HR',3500);
INSERT INTO Employee(name, department, salary) VALUES ('Employee7','HR',2400);
INSERT INTO Employee(name, department, salary) VALUES ('Employee8','HR',2200);
INSERT INTO Employee(name, department, salary) VALUES ('Employee9','HR',3300);
INSERT INTO Employee(name, department, salary) VALUES ('Employee10','HR',3300);
INSERT INTO Employee(name, department, salary) VALUES ('Employee11','Business Development',4500);
INSERT INTO Employee(name, department, salary) VALUES ('Employee12','Business Development',4500);
INSERT INTO Employee(name, department, salary) VALUES ('Employee13','Business Development',3200);
INSERT INTO Employee(name, department, salary) VALUES ('Employee14','Business Development',3200);
INSERT INTO Employee(name, department, salary) VALUES ('Employee15','Business Development',3200);

Student – (id, name, subject, marks [from 100])

CREATE TABLE Student (
   [id] int identity(1,1) not null Primary Key,
   [name] varchar(100),
   [subject] varchar(100),
   [marks] int
);
--not providing id value since its auto generated
INSERT INTO Student(name, subject, marks) VALUES ('Student1','Maths',99);
INSERT INTO Student(name, subject, marks) VALUES ('Student2','Maths',99);
INSERT INTO Student(name, subject, marks) VALUES ('Student3','Maths',100);
INSERT INTO Student(name, subject, marks) VALUES ('Student4','Maths',100);
INSERT INTO Student(name, subject, marks) VALUES ('Student5','Maths',87);
INSERT INTO Student(name, subject, marks) VALUES ('Student6','English',77);
INSERT INTO Student(name, subject, marks) VALUES ('Student7','English',77);
INSERT INTO Student(name, subject, marks) VALUES ('Student8','English',77);
INSERT INTO Student(name, subject, marks) VALUES ('Student9','English',80);
INSERT INTO Student(name, subject, marks) VALUES ('Student10','English',80);
INSERT INTO Student(name, subject, marks) VALUES ('Student11','Science',100);
INSERT INTO Student(name, subject, marks) VALUES ('Student12','Science',100);
INSERT INTO Student(name, subject, marks) VALUES ('Student13','Science',100);
INSERT INTO Student(name, subject, marks) VALUES ('Student14','Science',100);
INSERT INTO Student(name, subject, marks) VALUES ('Student15','Science',100);

DENSE_RANK() Without PARTITION BY Clause – The Nth Highest Record From the Entire Table

Let’s apply DENSE_RANK() to the Employee Table to rank the employees based on their salary in the following example with a SELECT statement.

SELECT
   [id],
   [name],
   [department],
   [salary],
   DENSE_RANK() OVER ( ORDER BY [salary] DESC ) as rank_based_on_salary 
FROM
   employee;

DENSE_RANK() to the Employee Table based on salary

The results show that the same salary values have the same rank assigned.

Now let’s extend the same query to get the employee with the second highest salary from the table.

select * from (
   SELECT
      [id],
      [name],
      [department],
      [salary],
      DENSE_RANK() OVER ( ORDER BY [salary] DESC ) as rank_based_on_salary 
   FROM
      employee
   ) as emp
where emp.rank_based_on_salary = 2

Let’s apply DENSE_RANK() to the Student table to rank the students based on their marks.

SELECT
   [id],
   [name],
   [subject],
   [marks],
   DENSE_RANK() OVER ( ORDER BY [marks] DESC ) as rank_based_on_marks 
FROM
   student;

As you can see, the same marks result in the same rank assigned to the students.

Now let’s extend the same query to find the student with the fourth highest marks from the table.

select * from (
   SELECT
      [id],
      [name],
      [subject],
      [marks],
   DENSE_RANK() OVER ( ORDER BY [marks] DESC ) as rank_based_on_marks 
   FROM
      student
   ) as stu
where rank_based_on_marks = 4

Here is the generic query to find the nth highest record from the table using DENSE_RANK() without the PARTITION BY clause:

select * from (
   SELECT
      column_name1,
      column_name2,
      column_name3,
      DENSE_RANK() OVER ( ORDER BY col_based_on_ranking DESC ) as [rank] 
   FROM
      table_name
   ) as temp_table_name
where [rank] = nth

DENSE_RANK() with PARTITION BY Clause – The Nth Highest Record Based on Partition

Let’s apply DENSE_RANK() to the Employee table to rank the employees in each department based on their salary.

SELECT
   [id],
   [name],
   [department],
   [salary],
   DENSE_RANK() OVER ( PARTITION BY [department] ORDER BY [salary] DESC ) as rank_based_on_salary 
FROM
   employee;

DENSE_RANK() to the Employee Table - rank employees by department based on salary

In this case, the ranking of each employee is specific to each department.

Now let’s extend the same query to find the employee with the second highest salary in each department from the table.

select * from (
   SELECT
      [id],
      [name],
      [department],
      [salary],
      DENSE_RANK() OVER ( PARTITION BY [department] ORDER BY [salary] DESC ) as rank_based_on_salary 
   FROM
      employee
   ) as emp
where emp.rank_based_on_salary = 2

DENSE_RANK() to the Employee Table - 2nd highest salary by department

Let’s apply DENSE_RANK() to the Student table to rank the students for each subject based on their marks.

SELECT
   [id],
   [name],
   [subject],
   [marks],
   DENSE_RANK() OVER ( PARTITION BY [subject] ORDER BY [marks] DESC ) as rank_based_on_marks 
FROM
   student;

DENSE_RANK() to the Student Table - rank students by subject based on marks

In this case, the ranking of each student is specific to each subject.

Now let’s extend the same query to find the student with the third highest marks in each subject from the table. From the results above, it’s clear that the only subject (maths) has a student with the third highest marks so that the nth highest query will bring only one student in maths.

select * from (
   SELECT
      [id],
      [name],
      [subject],
      [marks],
      DENSE_RANK() OVER ( PARTITION BY [subject] ORDER BY [marks] DESC ) as rank_based_on_marks 
   FROM
      student
   ) as stu
where rank_based_on_marks = 3

Here is the generic SQL statement to get the nth highest record from the table using DENSE_RANK() with PARTITION BY clause:

select * from (
   SELECT
      column_name1,
      column_name2,
      column_name3,
      DENSE_RANK () OVER ( PARTITION BY col_based_on_partitioning ORDER BY col_based_on_ranking DESC ) as [rank] 
   FROM
      table_name
   ) as temp_table_name
where [rank] = nth

Conclusion

This tip highlights the steps to get the nth highest record from a table. From the two scenarios above, we can rank all the items in the table in any category, i.e., department in the employee table and subject in the student table. However, if ranking is specific to each category, it can also be achieved by adding the PARTITION BY clause.

In our day to day world, there are many use cases that require nth highest record; hence DENSE_RANK() can be used smartly to fulfill the need.

Next Steps

Additional reading

Resources

Community

Subscribe