CODEWITHSUNDEEP
sqloracle-database
Jay Sun
Jay Sun

Reputation: 1601

Categorizing a column value by user in SQL

Let's say I have a table that looks like this:

Scores
-----------------
User varchar(100)
Score int

And the table is seeded with data that looks like this:

'Jay' | 80
'Jay' | 90
'Jay' | 95
'Jay' | 73
'Max' | 95
'Max' | 80
'Max' | 75

I need a query that selects every row of the table and then does a comparison of scores by user. If the score is the only score or the highest score, it gets categorized as "High". If a user has multiple scores and this particular score instance is the lowest one, it's categorized as "Low". Every other score gets categorized as "meh". What would the query look like? I want the output to look like this:

'Jay' | 80 | 'Meh'
'Jay' | 90 | 'Meh'
'Jay' | 95 | 'High'
'Jay' | 73 | 'Low'
'Max' | 95 | 'High'
'Max' | 80 | 'Meh'
'Max' | 75 | 'Low'

Upvotes: 2

Views: 94

Answers (1)

dnoeth
dnoeth

Reputation: 60482

You need a Windowed Aggregate Function, a Group MIN/MAX:

case
   when score = max(score) 
                over (partition by user) 
   then 'High' 
   when score = min(score) 
                over (partition by user) 
   then 'Low' 
   else 'Meh'
end

Upvotes: 3

Related Questions