How can I get second largest column value and its name?
My current query gives it mostly correct but in cases where largest and second largest values are same I am getting wrong values.
select item_code, A, B, C,
greatest(A, B, C) as largest1,
greatest(case when largest1 = A then 0 else A end,
case when largest1 = B then 0 else B end,
case when largest1 = C then 0 else C end) as largest2,
(case largest1 when A then 'A'
when B then 'B'
when C then 'C' end) as largest1_column_name,
(case largest2 when A then 'A'
when B then 'B'
when C then 'C' else 'None' end) as largest2_column_name
from table1
Below is the sample table:
+-----------+----+----+----+
| item_code | A | B | C |
+-----------+----+----+----+
| p1 | 20 | 30 | 40 |
| p2 | 50 | 30 | 10 |
| p3 | 30 | 50 | 10 |
| p4 | 30 | 30 | 30 |
| p5 | 50 | 50 | 10 |
| p6 | 0 | 0 | 0 |
+-----------+----+----+----+
Below is expected output:
+-----------+----+----+----+----------+----------+----------------------+----------------------+
| item_code | A | B | C | largest1 | largest2 | largest1_column_name | largest2_column_name |
+-----------+----+----+----+----------+----------+----------------------+----------------------+
| p1 | 20 | 30 | 40 | 40 | 30 | C | B |
| p2 | 50 | 30 | 10 | 50 | 30 | A | B |
| p3 | 30 | 50 | 10 | 50 | 30 | B | A |
| p4 | 30 | 30 | 30 | 30 | 30 | A | B |
| p5 | 50 | 50 | 10 | 50 | 50 | A | B |
| p6 | 0 | 0 | 0 | 0 | 0 | A | B |
+-----------+----+----+----+----------+----------+----------------------+----------------------+
This is the output I am getting from my query (I have marked wrong as comment):
+-----------+----+----+----+----------+-------------+----------------------+----------------------+
| item_code | A | B | C | largest1 | largest2 | largest1_column_name | largest2_column_name |
+-----------+----+----+----+----------+-------------+----------------------+----------------------+
| p1 | 20 | 30 | 40 | 40 | 30 | C | B |
| p2 | 50 | 30 | 10 | 50 | 30 | A | B |
| p3 | 30 | 50 | 10 | 50 | 30 | B | A |
| p4 | 30 | 30 | 30 | 30 | 0/*wrong*/ | A | NULL/*wrong*/ |
| p5 | 50 | 50 | 10 | 50 | 10/*wrong*/ | A | C/*wrong*/ |
| p6 | 0 | 0 | 0 | 0 | 0/*wrong*/ | A | A/*wrong*/ |
+-----------+----+----+----+----------+-------------+----------------------+----------------------+
I tried a slight variation of this (listagg instead of string_agg) in Snowflake and it seemed to be getting the expected result
with cte (item_code, abc, id) as
(select item_code, a, 'a' from table1 union all
select item_code, b, 'b' from table1 union all
select item_code, c, 'c' from table1)
select item_code,
max(case when id='a' then abc end) a,
max(case when id='b' then abc end) b,
max(case when id='c' then abc end) c,
split_part(string_agg(abc::varchar,',' order by abc desc),',',1) largest1,
split_part(string_agg(abc::varchar,',' order by abc desc),',',2) largest2,
split_part(string_agg(id,',' order by abc desc),',',1) largest1_col,
split_part(string_agg(id,',' order by abc desc),',',2) largest2_col
from cte
group by item_code;