secondmax

How do I retrieve the second highest salary of emp?

There is no right or wrong treatment of duplicates, there are only buggy specifications…

There are plenty of ways to do this.

An Oracle 7 approach would be
SELECT MAX(SAL)
FROM EMP, (
SELECT MAX(SAL) MAXSAL FROM EMP)
WHERE SAL

Using TOP-n queries I could use

SELECT SAL
FROM (
SELECT ROWNUM R,SAL FROM (
SELECT SAL FROM EMP ORDER BY SAL DESC))
WHERE R=2;

Using ROW_NUMBER (or RANK or DENSE_RANK), I could use

SELECT SAL
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) R,SAL FROM EMP)
WHERE R=2;

Another approach would be to create a SecondMax aggregate function, as the following example from the doc
Example 11-12 How to Create and Use a User-Defined Aggregate Function
SELECT SecondMax(sal)
FROM emp;

Ok, if I know I have 14 employees in EMP, retrieving the second highest is way easier 🙂


SELECT PERCENTILE_DISC(2/14) WITHIN GROUP (ORDER BY SAL DESC)
FROM EMP;

13 Comments

Leave a Reply

Your email address will not be published.