Find out Nth Largest Salary in SQL?

Problem Defination :- We have a Employee table in database and it has few columns including salary of Employee also here we need to find out the Nth highest Salary from table using a Query in SQL Server.

This is the most asked interview question in software developers interviews and many people were not able to answer this simple question. In this article I am going to write a Query with using the Dense_Rank function on it and will explain it in details.

Dense_Rank() :- Dense_Rank() is a inbuilt function in SQL Server is used for compute the row numbers in a table based on Order by Clause that is passed in Over() function and it returns the integer value for each row that is Row Number.

How to write Query with Dense_Rank() ?

Implementation Details :- Firstly we need to create a Employee table with EmplyeeId, EmpName, Age and Salary columns and then Insert data into Employee table and write the below query to create a table .

CREATE TABLE tblEmployee (
    EmployeeId int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NOT NULL,
    Salary int NOT NULL
) 

Insert Data into Employee table as shown in below image

Write and run the below query

select * from (select EmployeeId,Name,Salary,Dense_Rank() 
over(order by Salary desc) as RowsNumber 
from tblEmployee) as QueryResult  

Output of the above query as you can see in this image Dense_Rank() will compute the row number as integer values from desc to asc.

Now our main concern with N th highest Salary so below I have written a Query , In which you can see RowsNumber=4 that means 4 th highest salary from Emplyee table. If you want to find out 7 th largest salary then you have to put RowsNumber=7 like that you can find whatever the value of N th.
Dense_Rank() works with Over() function in which you need to define the Order By Clause for Salary.

select * from (select EmployeeId,Name,Salary,Dense_Rank() 
over(order by Salary desc) as RowsNumber 
from tblEmployee) as QueryResult 
where RowsNumber=4 

Final Output of Query