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