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