Tag Archives: with plsql

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