CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Dmitry Isaev
Dmitry Isaev

Reputation: 3998

Get Top N results for each column value

I've got a table like this:

+--------+--------+
|  name  |  kind  |
+--------+--------+
| cat    | animal |
| dog    | animal |
| horse  | animal |
| ant    | animal |
| bird   | animal |
| tree   | plant  |
| grass  | plant  |
| carrot | plant  |
|                 |
|     ... etc.    |
+--------+--------+

How can I get Top N items of each kind? E.g. N=2:

+--------+--------+
|  name  |  kind  |
+--------+--------+
| ant    | animal |
| bird   | animal |
| carrot | plant  |
| grass  | plant  |
+--------+--------+

Thanks in advance!

Upvotes: 1

Views: 217

Answers (1)

valex
valex

Reputation: 24134

Here is a SqlFiddle demo:

select name,kind from
(

select Name,Kind,
      @i:=if(@kind=kind,@i+1,1) rn,
      @kind:=kind 
      from t, (select @i:=0,@kind:='') d 
      order by Kind,name
) t1
where t1.rn<=2
     order by Kind,name

Upvotes: 3

Related Questions