Get the secondmax, again

Just bouncing on 2008/07/secondmax.

Another way of getting secondmax would be with an ordered collection. While collection methods like (n), first, last, count are not in SQL, I used PLSQL (within SQL)


WITH FUNCTION f (c sys.odcinumberlist, n NUMBER) RETURN number
IS BEGIN RETURN c (n); END;
SELECT
f(
CAST(
COLLECT(
CAST(
sal AS NUMBER
) ORDER BY sal DESC
)
AS SYS.odcinumberlist
),
2
) as secondmax
FROM emp;

SECONDMAX
----------
3000

Another 12c syntax would be


SELECT sal secondmax
FROM emp
ORDER BY sal DESC
OFFSET 1 ROW
FETCH FIRST 1 ROW ONLY;

SECONDMAX
----------
3000