Question 202064 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL select max items by grouping on department

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.

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    A simple query that i can crate is:

    SELECT * 
FROM myTable
GROUP BY department
HAVING MAX(rating)

    A website that explain very well this concept is this.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search