## Interview Question: Get the 2nd highest salary in the company

Question: Get the 2nd highest salary in the company.

Question level: Intermediate

We picked just 6 workarounds for this fairly simple problem. Overall, there are at least 15 different approaches available.

Method/Workaround #1 (Level: Beginner)

```SELECT MAX(SAL)
FROM (SELECT SAL
FROM EMP
WHERE SAL<(SELECT MAX(SAL)
FROM EMP)
)
```

Method/Workaround #2 (Level: Intermediate)

```SELECT sal
FROM (SELECT sal, ROWNUM rn
FROM (SELECT SAL
FROM EMP
GROUP BY SAL
ORDER BY 1 DESC)
WHERE ROWNUM<=2
)
WHERE RN=2
```

Method/Workaround #3 (Level: Intermediate)

```SELECT DISTINCT sal
FROM (SELECT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
FROM EMP)
WHERE RK=2
```

Method/Workaround #4 (Level: Intermediate)

```SELECT SAL
FROM (SELECT DISTINCT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
FROM EMP)
WHERE RK=2
```

```SELECT A.SAL
FROM EMP A JOIN EMP B ON A.SAL<=b.SAL
GROUP BY a.sal
HAVING COUNT(DISTINCT b.sal)=2
```

Method/Workaround #6 (Level: Intermediate)

```SELECT DISTINCT SAL
FROM emp a
WHERE 2=(SELECT COUNT(DISTINCT sal)
FROM emp b
WHERE b.sal>=a.sal)
```

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.