26

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?

Community
  • 1
  • 1
user319088
  • 261
  • 1
  • 3
  • 3
  • 3
    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). – Jonathan Leffler Apr 17 '10 at 07:40
  • @Jonathan: This is the "forever question" in the field of beginner SQL questions. There's a fresh one every day on SO. – Tomalak Apr 17 '10 at 08:11

3 Answers3

38
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
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • 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. – user319088 Apr 17 '10 at 06:37
  • 2
    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. – Tomalak Apr 17 '10 at 07:23
  • 3
    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 Apr 17 '10 at 07:36
6

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
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
0

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

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
)
Claudia
  • 576
  • 2
  • 7
  • 1
    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. – Jonathan Leffler Apr 17 '10 at 15:34
  • 1
    @Jonathan +1 solely for using the diacritical i in "naïve". :-) – Tomalak Apr 19 '10 at 15:09