SQL select max items by grouping on department

Question

I have a table mytable with name, department, rating

example:

a, d1, 3
b, d1, 5
c, d1, 10
a1, d2, 4
a2, d2, 1
a3, d2, 5

I want the output to group the output by department and pick the one with highest rating and display like this:

c, d1, 10
a3, d2, 5

I tried like this:

select name, department, max(rating) from mytab
group by department.

But this query is wrong as I need to add name also to group by clause. What is the right way to solve this.

score 1 · Answer 1 · answered Jul 06 '23 at 18:03

1

This will give you all the records with max rating within a department, so if two have the max rating, you will see both.

In the subquery you get the max values per department and then you just select all records with that value

select mytable.*
FROM  mytable
   JOIN (SELECT department, MAX(rating) AS rating 
         FROM mytable 
         GROUP BY department) maxvals
   USING (department, rating)

answered Jul 06 '23 at 18:03

Roemer

1,124
8
23

what is the use of `Using` keyword and how it works? – learner Jul 06 '23 at 18:32
Dear learner, to learn basics, Google is faster than asking . ;) – Roemer Jul 07 '23 at 16:10

SQL select max items by grouping on department

1 Answers1