Nth Highest Salary in Employee Table

-- Query to find the Nth Highest Salary in Employee Table

SELECT EMPNO, ENAME, SAL

FROM

(

SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS N,

EMPNO, ENAME, SAL FROM EMP

) DT

WHERE N = 3

I used N=3, as I need the third highest salary. We can check the data for various ranks.

Also check how the below window functions return data differently.

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

SELECT RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP