仅选择一个字段的最高值的行，并按另一字段分组 [英] Selecting only rows with the highest value of one field, grouped by another field

问题描述

我有一个表，其信息结构如下:

I have a table that has information structured like this:

ID  Points  Name    School
1   123     James   A
2   534     Henry   B
3   56      Henry   B
4   153     Chris   B
5   95      Chris   B
6   83      Chris   B
7   421     James   A

我需要从查询中删除具有相同名称的行，但每个行的最高点才是这样的:

And I need to get out of a query the rows that have the same name, but only the highest points for each like this:

ID  Points  Name    School
7   421     James   A
2   534     Henry   B
4   153     Chris   B

关于如何通过查询完成此操作的任何想法?我花了太多时间试图解决这个问题.

Any ideas on how this could be accomplished with a query? I've spent way too much time trying to figure this out.

推荐答案

select name,school,max(points) from table group by name,school

这将为您提供每个名称/学校组合的最高分.如果需要ID，可以将其加入自身:

That will give you the max points per name/school combination. Join it to itself if you want the ID:

select table.* from table inner join
(select name,school,max(points) as points from table group by name,school) a
on a.name = table.name and a.school = b.school and a.points = table.points

edit:对不起，这是一个SQL解决方案...刚刚看到了MSACCESS标记.逻辑是正确的，但是您需要转换为访问语法.

edit : sorry, this is a SQL solution...just saw the MSACCESS tag. Logic is right, but you'll need to convert to access syntax.

修改以纠正第二个查询，错过了我的联接中的一列

edit to correct the second query, missed a column inh my join

这篇关于仅选择一个字段的最高值的行，并按另一字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！