MySQL Group By with top N number of each kind

Question

I have a table like this:

Rank      Letter
1         A
2         A
3         B
4         A
5         C
6         A
7         C
8         C
9         B
10        C 

And I need the top 2 of each letter ordered by ascending rank:

Rank      Letter
1         A
2         A
3         B
5         C
7         C
9         B

How would I do it? It's fairly straightforward to get just the top 1 using GROUP BY, but I can't seem to get it working for multiple entries

Answer 1

SELECT  mo.Letter, md.Rank
FROM    (
        SELECT  DISTINCT letter
        FROM    mytable
        ) mo
JOIN    mytable md
ON      md.Letter >= mo.Letter
        AND md.Letter <= mo.Letter
        AND Rank <=
        (
        SELECT  Rank
        FROM    mytable mi
        WHERE   mi.letter = mo.letter
        ORDER BY
                Rank
        LIMIT 1, 1
        )

You need to have a composite index on (Letter, Rank) (in this order)

Note this construct:

md.Letter >= mo.Letter
AND md.Letter <= mo.Letter

instead of mere md.Letter = mo.Letter

It forces Range checked for each record which is more efficient.

See this article in my blog:

• Advanced row sampling

for more details on this.

Answer 2

select distinct rank, letter
  from table1 t2
 where rank in 
         (select top 2 rank
            from table1 t2 
           where t2.letter = t1.letter 
           order by rank)
       order by letter, rank

EDIT: (my first try won't work on MySql (Quassnoi comment), I modified it to work on sql server for example)

second try:

select t.letter, t.rank
from table1 t
join (
    select t1.letter, min(t1.rank) m
    from table1 t1
    join (select t0.letter, min(t0.rank) m, count(1) c 
           from table1 t0 group by t0.letter) t2
    on t1.letter = t2.letter and ((t2.c = 1) or (t2.c > 1 and t1.rank > m))
    group by t1.letter) t3 
  on t.letter = t3.letter and t.rank <= t3.m

Answer 3

Here's one for MySql, I'm having trouble figuring out...

Premiss: I want folks to donate money for specific weight class. NCAA wrestling has 10 weight classes. Of all records (rows) I only want to output the top 3 donors (ordered by Amount desc) for each WeightClass.

(I've got it working as long as there's only 3 rows for each weight class in the table.)

table = FightLadder columns = ID, Name, State, WeightClass, Amount

You can view current output at http://www.saveoregonwrestling.org/fightLadder

Here's my php code to extract the data out of my table -- then you see my code to store the data in vars so I can display them in html, (This is only for the first of 10 weight classes).

[code]

$query="SELECT c.PublishName, c.State, c.Total, c.WeightClass, d.ranknum FROM FightLadder AS c INNER JOIN ( SELECT a.ID, COUNT() AS ranknum FROM FightLadder AS a INNER JOIN FightLadder AS b ON (a.WeightClass = b.WeightClass) AND (a.Total <= b.Total) GROUP BY a.ID HAVING COUNT() <= 3 ) AS d ON (c.ID = d.ID) ORDER BY c.WeightClass, c.Total DESC, d.ranknum";

$result = mysql_query($query) or die("Failed Query of " . $query); //do the query

$champ1a = @mysql_result($result, 0,0); $champ2a = @mysql_result($result, 0,1); $champ3a = @mysql_result($result, 0,2); $runner1a = @mysql_result($result, 1,0); $runner2a = @mysql_result($result, 1,1); $runner3a = @mysql_result($result, 1,2); $consol1a = @mysql_result($result, 2,0); $consol2a = @mysql_result($result, 2,1); $consol3a = @mysql_result($result, 2,2);

[/pre]

I do the above vars for A-J. Is there a smarter more efficient way to extract this data? Hopefully, someone can help me out here. BTW - I resorted to using the @ in front of the mysql_result() function because I get "WARNING mysql_result() [something about pointer not able to find next row]" as soon as there's more or less than 30 rows in my table... And the @ symbol eliminates these nagging messages.