SQL Query Max Value Across Multiple Columns Multiple Rows

Question

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

do you need the max value per userid per teamname or just per userid? — Vamsi Prabhala, Nov 03 '16 at 22:52

score 1 · Accepted Answer · answered Nov 04 '16 at 00:33

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

Thank you! This looks like it might do the trick. I will see if I can make this work. — user3788942, Nov 04 '16 at 01:22

score 0 · Answer 2 · answered Nov 03 '16 at 22:54

could be using case when

select 
   user_id
 , case when (blue > Red AND Blue > Green AND Blue > Orange AND Blue > Yellow) THEN Blue ELSE null END
 , case when (Red > Blue AND Red > Green AND Red > Orange AND Red > Yellow) THEN Red ELSE null END
 , case when (Green > Blue AND Green > Red AND Green > Orange AND Green > Yellow) THEN Green ELSE null END
 , case when (Orange > Blue AND Orange > Red AND Orange > Green AND Orange > Yellow) THEN Orange ELSE null END
 , case when (Yellow > Blue AND Yellow > Red AND Yellow > Green AND Yellow > Orange) THEN Yellow ELSE null END
from my_table

score 0 · Answer 3 · edited May 23 '17 at 12:01

0

In your case maybe a case when coupled with a subtable might help, altough there might be a better solution out there.

This post here might help you out.

edited May 23 '17 at 12:01

Community

1
1

answered Nov 03 '16 at 22:55

R.Eduard

26
5

SQL Query Max Value Across Multiple Columns Multiple Rows

3 Answers3