How to find Nth Highest Salary in SQL Server

This is one of the most commonly asked question in SQL Server interview that how to find the Nth highest salary of employee with their details?

Where N could be any number for e.g. 1, 2,3, ..and so on.

Let’s create a sample table named Employee and insert some records into a table.

CREATE TABLE Employee (EmpName varchar(10), salary int);

INSERT INTO Employee VALUES ('Molly', 1000); 
INSERT INTO Employee VALUES ('Rozer', 2000);
INSERT INTO Employee VALUES ('Jhonny', 3000); 
INSERT INTO Employee VALUES ('Williams', 4000); 
INSERT INTO Employee VALUES ('Ronaldo', 5000);
INSERT INTO Employee VALUES ('Ronaldo', 5000);
INSERT INTO Employee VALUES ('Ajay', 3000);

Fetching Nth Highest salary 

Following query uses Dense_Rank() function to get the 2nd highest salary.

SELECT EmpName, Salary FROM(

SELECT DENSE_RANK() OVER(ORDER BY Salary DESC) AS SNo, EmpName, Salary

FROM Employee

) Sal WHERE SNo = 2

 

As you can see, In employee table 2nd highest salary is 4000 and query returns the same value.

What happen if two or more employees having same salary, means if ties happen between any records then in this case this query will return all those employees details. As, query is using DENSE_RANK function which returns the same rank for ties.

In Employee table, we can see two employees having same salary which is 3000 and this a 3rd highest salary in this table.

Following statement returns a 3rd highest salary.

SELECT EmpName, Salary FROM (
SELECT DENSE_RANK() OVER(ORDER BY Salary DESC) AS SNo, EmpName, 
Salary FROM Employee 
) 
Sal WHERE SNo = 3