やりたいこと
MySQL 5.7 で、グループ毎にレコードに連番を振りたいです（一度の SQL 実行で）。
その際、各グループ内は指定した順でソートしておきたい。
ROW_NUMBER() について
行番号を振るときに使える ROW_NUMBER() が MySQL 5.7 にはありません。
そのため別の方法を考える必要があります。
調べていたら、SQL 中で変数を使って連番を振る方法が見つかりました。
ROW_NUMBER() を使わないでやる
以下のテーブルで試してみます。
mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name | created_at |
+----+----------+--------------------+------------+
| 1 | 1 | Cupcake | 2020-01-01 |
| 2 | 1 | Donut | 2020-01-02 |
| 3 | 2 | Eclair | 2020-01-03 |
| 4 | 2 | Froyo | 2020-01-04 |
| 5 | 2 | Gingerbread | 2020-01-05 |
| 6 | 3 | Honeycomb | 2020-01-06 |
| 7 | 4 | Ice Cream Sandwich | 2020-01-07 |
| 8 | 4 | Jelly Bean | 2020-01-08 |
| 9 | 4 | KitKat | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)
このテーブルで group_id 毎に連番を振ります。
同一 group_id 内では created_at の降順でソートします。
mysql5.7.sql
SELECT
@rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
history.id,
@prev_group_id := group_id AS group_id,
history.name,
history.created_at
FROM
history,
(select @rownum := 0) hoge,
(select @prev_group_id := '') fuga
ORDER BY
group_id,
created_at DESC
連番の算出は SQL 中で分岐して処理しています。
一つ前のレコードの group_id と値が同じなら 1 ずつ加算し、そうでないなら 1 から振り直す、という流れです。
実行結果は下記。
+--------+----+----------+--------------------+------------+
| rownum | id | group_id | name | created_at |
+--------+----+----------+--------------------+------------+
| 1 | 2 | 1 | Donut | 2020-01-02 |
| 2 | 1 | 1 | Cupcake | 2020-01-01 |
| 1 | 5 | 2 | Gingerbread | 2020-01-05 |
| 2 | 4 | 2 | Froyo | 2020-01-04 |
| 3 | 3 | 2 | Eclair | 2020-01-03 |
| 1 | 6 | 3 | Honeycomb | 2020-01-06 |
| 1 | 9 | 4 | KitKat | 2020-01-09 |
| 2 | 8 | 4 | Jelly Bean | 2020-01-08 |
| 3 | 7 | 4 | Ice Cream Sandwich | 2020-01-07 |
+--------+----+----------+--------------------+------------+
9 rows in set (0.00 sec)
いい感じに連番が振れました。
ROW_NUMBER() を使ってやる
MySQL 8 では ROW_NUMBER() が使えるため、下記のように書けます。
mysql8.sql
SELECT
ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY group_id, created_at DESC) AS rownum,
id,
group_id,
name,
created_at
FROM
history
ORDER BY
group_id,
created_at DESC
所感
SQL 内で変数を使うので、少しとっつきにくい感じがあると思いました。
ROW_NUMBER() が使えればよりシンプルに書けますが、諸般の事情で MySQL 8 系を利用できない場合もあるので、記憶の片隅に置いておくと良いと思います。