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.
Srinivas Ramakrishna
