How to get the 2 greatest values between multiple columns?

Question

I'm trying to figure out how to get the 2 highest values from 5 fields. I know about the greatest function, but I don't know how to pull out the second highest value, too.

Basically, the table has 5 NUMBER type fields. In this example, the last two columns are my desired results.

| Score1 | Score2 | Score3 | Score4 | Score5 | | Highest1_value | Highest2_value 
+--------+--------+--------+--------+--------+ +----------------+---------------
|    10  |    20  |    30  |    40  |   50   | |       50       |       40
|    20  |    20  |    12  |    17  |    0   | |       20       |       20
|     7  |     7  |     7  |     7  |   11.1 | |       11.1     |        7
|    10  |    10  |    10  |    10  |   10   | |       10       |       10

Answer 1

Unpivot the data and use row_number to get the first 2 highest scores per id.

select id
,max(case when rnum=1 then val end) as highest_1
,max(case when rnum=2 then val end) as highest_2
from (select id,score,val,row_number() over(partition by id order by val desc) as rnum
      from (select * from t --replace this with your tablename
            unpivot (val for score in (score1,score2,score3,score4,score5)) p
          ) tbl
      ) tbl
group by id

Answer 2

If I correct understand, you need this:

select your_table.*, REGEXP_SUBSTR(h.str, '^[^\-]+') AS h1, REGEXP_SUBSTR(h.str, '[^\-]+$') AS h2  FROM your_table
inner join  (
    select id, listagg(sc, '-') WITHIN GROUP (ORDER BY sc DESC) str FROM(
        select id, sc, row_number() over(partition by id order by sc desc) rn from (
            select id, sc FROM your_table
            UNPIVOT (sc for col in (Score1, Score2, Score3, Score4, Score5))
        ) tt
    )ttt
    where rn <= 2
    GROUP BY id
) h
ON your_table.id =  h.id