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?

Solution 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

Solution 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

Comments

[Jonathan Leffler]

I wonder how many duplicates there are for this query - certainly many more than just one. See the 60+ questions tagged 'greatest-n-per-group' (for the cases n=1).

[Tomalak]

@Jonathan: This is the "forever question" in the field of beginner SQL questions. There's a fresh one every day on SO.

[user319088]

hi sir, thanks for your quick reply. i also created one query just now, and its giving me perfect result. here is my query select Name, Top, total from animals where total in(SELECT max(total) FROM animals group by name) group by name my question is, which is more efficient, yours or mine when table contain 2 millions of data? thanks again for your reply.

[Tomalak]

Which is more efficient? Define proper indexes on your table and try it out. Apart from that, your WHERE total in (...) is wrong. You would quickly see this once you try with actual millions of records and not just a hand full.

[Jonathan Leffler]

The second query is likely to more efficient than the first because the first uses a correlated sub-query which might be executed many times instead of just once in the second version.

[Jonathan Leffler]

This will work, with the caveat about it being expensive on big tables with a naïve optimizer that does not manage to execute the correlated sub-query just once.

[Tomalak]

@Jonathan +1 solely for using the diacritical i in "naïve". :-)