How to find nth highest salary using SQL query

This is a common SQL question which will be asked in most of the interviews and also if you are working related to the financial application then most of them would have come across this scenario where you need to find Nth highest salary using SQL query or finding nth highest expenditure or something like that.

There are several ways to achieve this functionality lets look at all the possible ways.

SQL Query to find the highest salary of an employee:

Select Max(Salary) from Employees

SQL query to find 2nd Highest salary in the employee table using subquery

Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

SQL query to find nth highest salary in the employee table using subquery

 SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY
// Replace the N with the highest number which you need to find.

 find nth highest salary using SQL query in the employee table using CTE

 WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N

DENSE_RANK() will give you all the employees who have the Nth highest salary – no matter how many employees have the (identical) highest salary.

Leave a Reply

Your email address will not be published.

Sign Up for Our Newsletters

Get notified of the best deals on our WordPress themes.

You May Also Like
How To

How to configure Git proxy?

If you are working in the corporate company all the internet requests usually goes through the corporate firewall. The Git will simply throw an error saying “Request timeout” whenever you…
View Post
Convert A List To String In Python

Convert a list to string in Python

Table of Contents Hide Program to convert a list to string in PythonUsing join() methodUsing List ComprehensionIterating using for loopUsing map() method There are various scenarios where you would need…
View Post