英文:
SQL select max items by grouping on department
问题
我有一个名为mytable的表，其中包含name，department，rating。
示例：
a, d1, 3
b, d1, 5
c, d1, 10
a1, d2, 4
a2, d2, 1
a3, d2, 5
我希望输出按部门分组，并选择评分最高的一个，显示如下：
c, d1, 10
a3, d2, 5
我尝试过如下：
select name, department, max(rating) from mytab
group by department.
但这个查询是错误的，因为我需要将name也添加到group by子句中。解决这个问题的正确方法是什么。
英文:
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.
答案1
得分: 1
这将为您提供在一个部门内具有最高评分的所有记录，因此如果有两个具有最高评分的记录，您将看到它们。
在子查询中，您会获得每个部门的最大值，然后只需选择具有该值的所有记录
select mytable.*
FROM mytable
JOIN (SELECT department, MAX(rating) AS rating
FROM mytable
GROUP BY department) maxvals
USING (department, rating)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论