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 |
+----+-------+
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 |
+-------+----+