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.

0 Shares
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like
XOR in Python

XOR in Python

XOR Operator in Python is also known as “exclusive or”  that compares two binary numbers bitwise if two bits are…
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.…