CODEWITHSUNDEEP
sqlt-sql
user3788942
user3788942

Reputation: 93

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

Upvotes: 1

Views: 219

Answers (3)

imaCoden
imaCoden

Reputation: 477

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

Upvotes: 1

R.Eduard
R.Eduard

Reputation: 26

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.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 0

Related Questions