SQL Query to Find nth MIN and nth Max Salary

Find the 3rd MAX salary IN the emp table:


SELECT DISTINCT sal
FROM emp e1
WHERE 3 =
(SELECT count(DISTINCT sal)
FROM emp e2
WHERE e1.sal <= e2.sal);

Find the 3rd MIN salary IN the emp table


SELECT DISTINCT sal
FROM emp e1
WHERE 3 =
(SELECT count(DISTINCT sal)
FROM emp e2
WHERE e1.sal >= e2.sal);

Click here for more Complex SQL Queries

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.