読者です 読者をやめる 読者になる 読者になる

破棄されたブログ

このブログは破棄されました。

MySQL で ORDER BY してから GROUP BY するっぽいことをする

StackExchange がなかったら死んでた MySQL SQL

注記 (2014-10-06)

`GROUP BY` で指定されてないカラムを `SELECT` した挙句 `ORDER BY` しようという考えが自体が邪道なので、本当に必要な場合以外は、別のクエリを考えるべき。
実際、 PostgreSQL では `GROUP BY` されていないカラムを `SELECT` することはできない。 SQL は集合に対する操作であることを再度認識しなければならない。

また、本文で行う操作は、下記のクエリで同様の結果が得られるので、特殊なケースでなければ全くの無駄である。

SELECT MAX(id) id, gid FROM demo GROUP BY gid;
+------+------+
| id   | gid  |
+------+------+
|    3 |    1 |
|    6 |    2 |
|    9 |    3 |
+------+------+

本文


主キーに id をグループ番号として gid を設定したテーブルで、 id を降順でソートしてから gid でグループ化したい。

SELECT * FROM demo;
+----+-----+
| id | gid |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   2 |
|  6 |   2 |
|  7 |   3 |
|  8 |   3 |
|  9 |   3 |
+----+-----+

目標はこういう感じ。

+----+-----+
| id | gid |
+----+-----+
|  3 |   1 |
|  6 |   2 |
|  9 |   3 |
+----+-----+

ORDER BY は GROUP BY の後に処理されるので、普通に GROUP BY して ORDER BY するようなクエリはダメ。

SELECT * FROM demo GROUP BY gid ORDER BY id;
+----+-----+
| id | gid |
+----+-----+
|  1 |   1 |
|  4 |   2 |
|  7 |   3 |
+----+-----+

そこで、同一テーブルを外部結合 (LEFT JOIN) する。結合時の条件は、主キーとソートに使いたいキー。今回でいうと id が主キーに gid がソートに使うキーにあたる。

SELECT * FROM demo cur LEFT JOIN demo next ON cur.gid = next.gid AND cur.id < next.id;
+----+-----+------+------+
| id | gid | id   | gid  |
+----+-----+------+------+
|  1 |   1 |    2 |    1 |
|  1 |   1 |    3 |    1 |
|  2 |   1 |    3 |    1 |
|  3 |   1 | NULL | NULL |
|  4 |   2 |    5 |    2 |
|  4 |   2 |    6 |    2 |
|  5 |   2 |    6 |    2 |
|  6 |   2 | NULL | NULL |
|  7 |   3 |    8 |    3 |
|  7 |   3 |    9 |    3 |
|  8 |   3 |    9 |    3 |
|  9 |   3 | NULL | NULL |
+----+-----+------+------+

ソートに使うキーで結合元 (cur) と結合先 (next) で比較してやると、値が大きくなるにつれ結合先のカラム数がが減少していく。最終的には 0 になり、また、外部結合を行なっているため結合先 (next) のカラムの値が NULL となる。

SELECT * FROM demo cur LEFT JOIN demo next ON cur.gid = next.gid AND cur.id < next.id WHERE next.id IS NULL;
+----+-----+------+------+
| id | gid | id   | gid  |
+----+-----+------+------+
|  3 |   1 | NULL | NULL |
|  6 |   2 | NULL | NULL |
|  9 |   3 | NULL | NULL |
+----+-----+------+------+

結合先 (next) で NULL になっているカラムだけを取り出せば期待した結果が得られる。

SELECT cur.* FROM demo cur LEFT JOIN demo next ON cur.gid = next.gid AND cur.id < next.id WHERE next.id IS NULL;
+----+-----+
| id | gid |
+----+-----+
|  3 |   1 |
|  6 |   2 |
|  9 |   3 |
+----+-----+
広告を非表示にする