Microsoft SQL Server Common Table Expressions Find nth highest salary using CTE
Example
Employees table :
| ID | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
| 1 | Jahangir | Alam | Male | 70000 |
| 2 | Arifur | Rahman | Male | 60000 |
| 3 | Oli | Ahammed | Male | 45000 |
| 4 | Sima | Sultana | Female | 70000 |
| 5 | Sudeepta | Roy | Male | 80000 |
+------+-----------+----------+--------+--------+
CTE (Common Table Expression) :
WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 1
To find 2nd highest salary simply replace N with 2. Similarly, to find 3rd highest salary, simply replace N with 3.
