SQL：查找每组的最大记录[重复]

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选择Total值最高的记录，所以我的结果应该是这样的：

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?

按名称顺序尝试了小组，但是按照结果给了小组的最高记录。有人可以指导我吗？

3 个解决方案

#1

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

要么

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

#2

You can try something like

你可以尝试类似的东西

SELECT  s.*
FROM    sometable s INNER JOIN
        (
            SELECT  Name,
                    MAX(Total) MTotal
            FROM    sometable
            GROUP BY Name
        ) sMax  ON  s.Name = sMax.Name 
                AND s.Total = sMax.MTotal

#3

Or using an Exists clause, wich returns the only row that exists in both tables

或者使用Exists子句，返回两个表中唯一存在的行

SELECT * from sometable T
where exists
(select 1
from (SELECT nombre, max(total) as total FROM  sometable TT
    GROUP by nombre) TT
where T.name=TT.name
and T.total=TT.total
)

#1

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

要么

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

#2