CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Don P
Don P

Reputation: 63637

SQL: return row with maximum value per group

I want to get the rows with the maximum values for each person. Using MySQL. 

person       page       views
John         home       20
John         cart       15
John         search     43
James        home       32
James        about      41
Kim          cart       5
Kim          contact    3

Result

Person   Page
John     search
James    about
Kim      cart

One thought is to get the MAX(views) GROUP BY PERSONand then do IF(a.views = b.max_views, page, 0) but this seems like it would require 3 steps and there is probably an easier way.

Upvotes: 1

Views: 1385

Answers (3)

Akash
Akash

Reputation: 5012

Try this

select 
    * 
from 
   data d
where d.views =  ( select max(views) from data d2 where d2.person = d.person )

Upvotes: 0

Taryn
Taryn

Reputation: 247690

You can use a subquery to get the result:

select t1.person, t1.page
from yt t1
inner join
(
  select max(views) maxview, person
  from yt
  group by person
) t2
  on t1.person = t2.person
  and t1.views = t2.maxview;

See SQL Fiddle with Demo

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

 Select * From table t
 where views =
     (Select Max(views) From table
      Where person = t.person)

Upvotes: 3

Related Questions