ORACLE: QUERY TO FIND Nth HIGHEST SALARY (in all or in each DEPARTMENT)

Below query will give you N highest salary wrt all Departments


SELECT * FROM
(SELECT EMP.*, ROW_NUMBER() OVER (ORDER BY SALARY DESC) RN FROM EMPLOYEE EMP) EMP_RANK
WHERE EMP_RANK.RN = N;

Below query will give you N highest salart wrt to each department


SELECT * FROM

(SELECT EMP.*, DENSE_RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY) RN
FROM EMPLOYEE EMP) EMP_RANK

WHERE EMP_RANK.RN = N;

Happy Kooding… Hope this helps!

Advertisements