Select column names based on max value by row

Question

I need select column name based on max value by row. I have snowflake database table:

id  cars_cat1   cars_cat2   cars_cat_3
a01     5           2           8
a02     6           1           5
a03     6           5           12
a04     3           9           1

Where is IDs and many categories with counts. I need new column, with category name, where count is max.

Output:

id  max_category
a01 cars_cat_3
a02 cars_cat1
a03 cars_cat_3
a04 cars_cat2

I try window functions... without success.

user_10498828 · Answer

You can use the function GREATEST() in a CASE expression:

SELECT id,
       CASE GREATEST(cars_cat1, cars_cat2, cars_cat_3)
         WHEN cars_cat1 THEN 'cars_cat1'
         WHEN cars_cat2 THEN 'cars_cat2'
         WHEN cars_cat3 THEN 'cars_cat3'
       END max_category
FROM tablename

Select column names based on max value by row

1 Answers