Find nth highest salary in SQL
January 27, 2023 2023-01-31 10:38Find nth highest salary in SQL
Subhajit Let’s create an Employee table which has a salary column in it as follows
Now we can find the nth highest salary from the above Employee using the following ways
1. Using CTE
The code below will find the 3rd highest salary in SQL from the EMPLOYEE table.
WITH RESULT AS
(
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DENSERANK
FROM EMPLOYEE
)
SELECT TOP 1 Salary
FROM RESULT
WHERE RESULT.DENSERANK = 3
Which will give the following result
Explanation
Here in the above query the CTE will hold the result from the following command
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DENSERANK
FROM EMPLOYEE
The result of the above command will be as follows
Now from the above result we will fetch only that salary whose DENSERANK will be 3 by which we il we will get the 3rd highest salary, that is again as follows
2. Without using CTE
The code below will fetch the nth highest value from the EMPLOYEE table without using CTE
SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP (3) Salary FROM EMPLOYEE ORDER BY Salary DESC
) AS result
ORDER by Salary;
The result will be as follows
Explanation
In the above query the subquery is as follows
SELECT DISTINCT TOP (3) Salary FROM EMPLOYEE ORDER BY Salary DESC
The above query will fetch the top 3 highest salary from the employee table as follows
Now from the above result we will fetch the top salary in ascending order, which will fetch the 3rd highest salary from the EMPLOYEE table as follows
Please comment and share if you like this post and tell us about how we can enhance our posts in dotnetcoaching. Thanks.
