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 
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
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
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.
