SQL: Find the max record per group

Question

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?

Answer 1

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