user3788942 user3788942 - 1 month ago 17
SQL Question

SQL Query Max Value Across Multiple Columns Multiple Rows

I need to write a sql query that will return the row that has the max value per user id.

In my table I have 5 columns (Blue, Red, Green, Blue, Orange, Yellow) that store a numeric value. And in my table a user can appear on multiple rows.

For each user id, I need to determine which of the 5 columns (Blue, Red, Green, Blue, Orange, Yellow) has the highest value per user id. I have tired a few things, but am stuck.

If I need to provide additional information, please let me know.

See table below enter image description here

Answer

If you want to get fancy you can look into Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS on groups but a simple approach is to flatten the list. You can use a pivot or union like example below. Then use row_number to get the first in the list.

declare @tbl table (UserId int, Blue int, Red int, Green int, Orange int, yellow int)


insert into @tbl (UserId, Blue, Red, Green, Orange, Yellow)
values
(1, 1,9,4,3,4),
(2, 2,5,4,3,5),
(3, 3,4,9,3,3),
(4, 9,4,6,3,9),
(5, 2,4,5,2,9)
;
with flattenedCte as (
    select UserId, Blue [Count], 'Blue' Color from @tbl
    union
    select UserId, Red [Count], 'Red' Color from @tbl
    union
    select UserId, Green [Count], 'Green' Color from @tbl
    union
    select UserId, Orange [Count], 'Orange' Color from @tbl
    union
    select UserId, Yellow [Count], 'Yellow' Color from @tbl
)
,Sub as (
select
    UserId,
    Color,
    max([Count]) [Max of Count],
    ROW_NUMBER() over (partition by UserId order by max([Count]) desc) [Row number]
    from flattenedCte
        group by UserId,Color
)
select * from Sub where [Row number] = 1