SQL Finding five largest numbers instead of one Max in a table

Question

I have a table and I need to run a query that contains some aggregation Functions like Maximum , Average , Standard Deviation , ... but instead of one Maximum I should return 5 largest number.

the simplified query is something like this:

SELECT OSI_KEY , MAX(VALUE) , AVG(VALUE) , STDDEV(VALUE), variance(VALUE) 
FROM DATA_VALUES_5MIN_6_2013
GROUP BY OSI_KEY
ORDER BY OSI_KEY

and I need some Magical ;) Query like this:

    SELECT OSI_KEY , MAX1(VALUE) ,MAX2(VALUE) ,MAX3(VALUE) ,MAX4(VALUE) , MAX5(VALUE) ,
    AVG(VALUE) , STDDEV(VALUE), variance(VALUE) 
    FROM DATA_VALUES_5MIN_6_2013
    GROUP BY OSI_KEY
    ORDER BY OSI_KEY

I appreciate your considerations.

Answer 1

Oracle has an NTH_VALUE() function. Unfortunately, it is only an analytic function and not a window function. This leads to the strange construct of SELECT DISTINCT with a bunch of analytic functions:

SELECT DISTINCT OSI_KEY,
       MAX(VALUE) OVER (PARTITION BY OSI_KEY),
       NTH_VALUE(VALUE, 2) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_2,
       NTH_VALUE(VALUE, 3) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_3,
       NTH_VALUE(VALUE, 4) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_4,
       NTH_VALUE(VALUE, 5) OVER (PARTITION BY OSI_KEY ORDER BY VALUE DESC) as MAX_5,
       AVG(VALUE) OVER (PARTITION BY OSI_KEY),
       STDDEV(VALUE)  OVER (PARTITION BY OSI_KEY),
       variance(VALUE)  OVER (PARTITION BY OSI_KEY)
FROM DATA_VALUES_5MIN_6_2013
ORDER BY OSI_KEY;

You can also do this using conditional aggregation, with a row_number() or dense_rank() in a subquery.