CODEWITHSUNDEEP
sqlsql-server
Meysam
Meysam

Reputation: 18157

How to return multiple columns when using "group by"?

Suppose I have the following data:

itemID  catID   score     age
  1       1        9       5   
  2       1       10       7
  3       1        5      10
  4       2       18      11
  5       2       15      15

I want to return the age column, for the maximum score of each category:

 catID    score    age
   1       10       7   
   2       18      11

I have the following query:

select catID, max(score)
from items
group by catID

But I don't know how to include the age column as well.

Upvotes: 1

Views: 246

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You need a join on the subqiery for amx score

    select i.catID, i.score, i.age
    from   items i
    INNER JOIN  (
        select catID, max(score) score 
        from items
        group by catID

        ) t on t.catID = i.CatID and t.score = i.score

Upvotes: 1

Thom A
Thom A

Reputation: 95830

What you're after here isn't aggregation on score but the details of the person with the highest score. You can use a CTE and ROW_NUMBER to achieve this:

WITH CTE AS(
    SELECT catID,
           score,
           age,
           ROW_NUMBER() OVER (PARTITION BY catID ORDER BY score DESC) AS RN
    FROM items)
SELECT catID,
       score,
       age
FROM CTE
WHERE RN = 1;

If multiple people could have the same score, and you want to return them all, replace ROW_NUMBER with DENSE_RANK.

Upvotes: 3

Related Questions