mysql group by个数_MySQL Group By每个类别的前N个数字

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

解决方案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