GROUP毎の上位N位抽出

MySQLでGROUP BYした時に最初のN件を抽出する方法メモ

How to select the first/least/max row per group in SQL | Xaprb

に載っているユーザ変数を使う方法が簡単そう

テーブル

+----+-------+  
| id | chara |  
+----+-------+  
|  1 | A     |  
|  2 | C     |  
|  3 | B     |  
|  4 | C     |  
|  5 | B     |  
|  6 | D     |  
|  7 | C     |  
|  8 | D     |  
|  9 | F     |  
| 10 | C     |  
+----+-------+  

SQL

SET @num := 0, @chara := '';
SELECT
s.chara,
s.id
FROM
(SELECT 
id,
chara,
@num := IF(@chara = chara, @num + 1, 1) AS row_number,
@chara := chara AS dummy
FROM test5
ORDER BY chara,id) s
WHERE s.row_number <= 2;

結果

+-------+----+  
| chara | id |  
+-------+----+  
| A     |  1 |  
| B     |  3 |  
| B     |  5 |  
| C     |  2 |  
| C     |  4 |  
| D     |  6 |  
| D     |  8 |  
| F     |  9 |  
+-------+----+