How to get the 2 highest values ​​between multiple columns?

Question

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

In principle, the table has five fields of type NUMBER. 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

Do not use data and use row_numberto get the first 2 highest scores for an identifier.

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 understood correctly, 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

Answer 3

, :

SELECT 
   dt.*,
   CASE maxscore -- GREATEST on all but the highest value,
                 -- simplified to a "Valued Case" based on mathguy comment
      WHEN score1 THEN Greatest(score2, score3, score4, score5)
      WHEN score2 THEN Greatest(score1, score3, score4, score5)
      WHEN score3 THEN Greatest(score1, score2, score4, score5)
      WHEN score4 THEN Greatest(score1, score2, score3, score5)
      ELSE Greatest(score1, score2, score3, score4)
   END
FROM
 (
   SELECT t.*,
      Greatest(Score1,Score2,Score3,Score4,Score5) AS maxscore
   FROM tab t
 ) dt

@vkp @mathguy Derived Table/Inline View:

SELECT 
   t.*,
   Greatest(Score1,Score2,Score3,Score4,Score5) as Highest1_value,
   CASE Greatest(Score1,Score2,Score3,Score4,Score5)-- GREATEST on all but the highest value
      WHEN score1 THEN Greatest(       score2,score3,score4,score5)
      WHEN score2 THEN Greatest(score1,       score3,score4,score5)
      WHEN score3 THEN Greatest(score1,score2,       score4,score5)
      WHEN score4 THEN Greatest(score1,score2,score3,       score5)
      ELSE             Greatest(       score1,score2,score3,score4)
   END as Highest2_value
FROM tab t