select n-highest values from different columns of the same row-CodePudding

Consider I have a table like this (Category columns can be more)

Name    CATEGORY_1  CATEGORY_2  CATEGORY_3
John        8           65          3
Sally       0            5         12
Sarah      15           17          0

How can I find the find the highest values like this

Name    CATEGORY_1  CATEGORY_2  CATEGORY_3  first_highest   second_highest  third_highest
John        8          65          3         CATEGORY_2       CATEGORY_1     CATEGORY_3
Sally       0           5         12         CATEGORY_3       CATEGORY_2     CATEGORY_1
Sarah      15          17          0         CATEGORY_2       CATEGORY_1     CATEGORY_3

CodePudding user response：

Here's one option; read comments within code:

SQL> with
  2  test (name, cat1, cat2, cat3) as
  3    -- sample data
  4    (select 'John' ,  8, 65,  3 from dual union all
  5     select 'Sally',  0,  5, 12 from dual union all
  6     select 'Sarah', 15, 17,  0 from dual
  7    ),

  8  temp as
  9    -- make rows out of columns
 10    (select name, 1 cat_rn, cat1 cat from test union all
 11     select name, 2,        cat2 from test union all
 12     select name, 3,        cat3 from test
 13    ),
 14  srt as
 15    -- sort rows by CAT values; use ROW_NUMBER instead of RANK in case two (or more) values
 16    -- are equal
 17    (select name, cat_rn, cat,
 18       row_number() over (partition by name order by cat desc) rn
 19     from temp
 20    )
 21  -- finally:
 22  select a.name, a.cat1, a.cat2, a.cat3,
 23    max('cat' || case when s.rn = 1 then cat_rn end) first_highest,
 24    max('cat' || case when s.rn = 2 then cat_rn end) second_highest,
 25    max('cat' || case when s.rn = 3 then cat_rn end) third_highest
 26  from test a join srt s on a.name = s.name
 27  group by a.name, a.cat1, a.cat2, a.cat3;

NAME        CAT1       CAT2       CAT3 FIRST_HIGHEST   SECOND_HIGHEST  THIRD_HIGHEST
----- ---------- ---------- ---------- --------------- --------------- ---------------
John           8         65          3 cat2            cat1            cat3
Sally          0          5         12 cat3            cat2            cat1
Sarah         15         17          0 cat2            cat1            cat3

SQL>

CodePudding user response：

You can also use below solution for that purpose. It uses unpivot clause to unpivot the source data, and creates three columns containing the nth highest values respectively (via nth_value analytic function). Then, it re-joins that result with the source data to get back the three categories columns.

with SampleData ( Name, CATEGORY_1, CATEGORY_2, CATEGORY_3 ) as (
    select 'John' ,  8, 65,  3 from dual union all
    select 'Sally',  0,  5, 12 from dual union all
    select 'Sarah', 15, 17,  0 from dual
)

, SampleData_NTH as (
  select NAME, COL, VAL
    , NTH_VALUE( COL, 1) OVER ( PARTITION BY NAME ORDER BY VAL desc 
                rows between UNBOUNDED preceding and UNBOUNDED following ) first_highest
    , NTH_VALUE( COL, 2) OVER ( PARTITION BY NAME ORDER BY VAL desc 
                rows between UNBOUNDED preceding and UNBOUNDED following ) second_highest
    , NTH_VALUE( COL, 3) OVER ( PARTITION BY NAME ORDER BY VAL desc 
                rows between UNBOUNDED preceding and UNBOUNDED following ) third_highest
  from SampleData
  unpivot (
    val for col in (
        CATEGORY_1 as 'CATEGORY_1'
      , CATEGORY_2 as 'CATEGORY_2'
      , CATEGORY_3 as 'CATEGORY_3'
      )
    )
)
select t1.NAME
    , t1.CATEGORY_1, t1.CATEGORY_2, t1.CATEGORY_3
    , t2.FIRST_HIGHEST, t2.SECOND_HIGHEST, t2.THIRD_HIGHEST
from SampleData t1
join SampleData_NTH t2 
    on t1.name = t2.name
group by t1.NAME
    , t1.CATEGORY_1, t1.CATEGORY_2, t1.CATEGORY_3
    , t2.FIRST_HIGHEST, t2.SECOND_HIGHEST, t2.THIRD_HIGHEST
order by 1
;

demo