SQL:查找每组的最大记录

SQL: Find the max record per group(SQL:查找每组的最大记录)

问题描述

<块引用>

可能的重复:
检索每组中的最后一条记录

我有一张表，其中包含三个字段和数据.

<前>名称 , 顶部 , 总计猫 , 1 , 10狗 , 2 , 7猫 , 3 , 20马 , 4 , 4猫 , 5 , 10狗 , 6 , 9

我想为每个Name选择Total值最高的记录，所以我的结果应该是这样的:

<前>名称 , 顶部 , 总计猫 , 3 , 20马 , 4 , 4狗 , 6 , 9

我尝试按名称顺序按总数分组，但它给出了按结果分组的最高记录.谁能指导我好吗?

select名称、顶部、总计从有的在哪里Total = (select max(Total) from sometable i where i.Name = sometable.Name)

或

选择名称、顶部、总计从有的内部联接 (选择 max(Total) Total, Name从某个表按名称分组) 作为 max.Name = sometable.Name 和 max.Total = sometable.Total 上的最大值

Possible Duplicate:
Retrieving the last record in each group

I have one table, which has three fields and data.

Name  , Top , Total
cat   ,   1 ,    10
dog   ,   2 ,     7
cat   ,   3 ,    20
horse ,   4 ,     4
cat   ,   5 ,    10
dog   ,   6 ,     9

I want to select the record which has highest value of Total for each Name, so my result should be like this:

Name  , Top , Total
cat   ,   3 ,    20
horse ,   4 ,     4
Dog   ,   6 ,     9

I tried group by name order by total, but it give top most record of group by result. Can anyone guide me, please?

select
  Name, Top, Total
from
  sometable
where
  Total = (select max(Total) from sometable i where i.Name = sometable.Name)

or

select
  Name, Top, Total
from
  sometable
  inner join (
    select max(Total) Total, Name
    from sometable
    group by Name
  ) as max on max.Name = sometable.Name and max.Total = sometable.Total

这篇关于SQL:查找每组的最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！