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 <=
        COALESCE
                (
                (
                SELECT  Rank
                FROM    mytable mi
                WHERE   mi.letter = mo.letter
                ORDER BY
                        Rank
                LIMIT 1, 1
                ),
                0xFFFFFFFF
                )

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.