シラサヤ備忘館

最新日付のデータのIDを抽出する。




下記のようなテーブルデータがあったとする。


> SELECT * FROM t_contact;
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 1 | 5 | 2014-10-16 01:00:00 |
| 2 | 5 | 2014-10-16 02:00:00 |
| 3 | 6 | 2014-10-16 03:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+


これを、office_id別でregist_dateの最新日付のidを取得したい。

期待する結果↓
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+




このようなことをしたいときは以下のようにSQLを書く。

> SELECT id, office_id, regist_date FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id;


以下は上記のSQL文を見やすいようにインデントを付けたもの↓

SELECT
  id,
  office_id,
  regist_date
FROM
  (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1
GROUP BY
  office_id
ORDER BY
  id;

ポイントはFROM句の中でサブクエリを使っている点。
そのサブクエリでは何をしているかと言うとORDER BYを使ってテーブルの並び替えをしてるだけ。

この時点ではテーブルデータは以下のように逆に並び替えられている。
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 5 | 7 | 2014-10-16 05:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 3 | 6 | 2014-10-16 03:00:00 |
| 2 | 5 | 2014-10-16 02:00:00 |
| 1 | 5 | 2014-10-16 01:00:00 |
+----+-----------+---------------------+



次に、GROUP BYでoffice_idをグルーピングしている。
グルーピングすることによってoffice_idが同じ値のレコードは1レコードに集約されるのだが、
複数レコードが1行に集約される際先頭行のデータが利用される。

office_idが5のレコードだけで見てみると以下のようになる。

・グルーピング前
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 1 | 5 | 2014-10-16 01:00:00 |
+----+-----------+---------------------+

・グルーピング後
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
+----+-----------+---------------------+



あと注意すべき点は、サブクエリを使う際はテーブル名に別名をつけておくこと。
テーブルの別名はtemp1でもsubquery1でもなんでもよい。
テーブルに別名がないとSQLは動かないので必ず付ける必要がある。

あと別名を付ける場合は必ずAS句を使うようにすること。
このAS句は省略することもできるがSQL文が読みづらくなるので省略しない方が個人的にはお勧め。


最後に並び替えが逆になってしまっているので通常の並びに戻すためにORDER BYを使っている。


これで当初の希望する結果が返る。
・office_id別でregist_dateが最新レコードのみを取得した
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+






--
ちなみに先述したような結果を取り出すのにTEMPORARY TABLEを使っても同じことができる。
ただテンポラリテーブルは色々と制限があったりする。

ネストしすぎてSQLの可読性が下がるような場合を除いて、基本的に使わない方が良いのではないかと思っている。





--
最後に、一度グルーピングしてしまうと他の色々な操作がしづらくなる。
なので先ほどのSQL文をもう一階層ネストしてやると利用しやすくなる。

> SELECT * FROM (SELECT * FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id) AS temp2 WHERE ...;


うん、可読性下がるねえ…




あーあと某サイトで見たけどMAX関数を使ったやり方はダメ。
結果がおかしくなる。