3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【MySQL】GROUP BYとORDER BYが併用できない件

Posted at

##経緯
一番新しく更新されたデータを取得したいと思い、こんな感じのSQLを書いたところ想定通りの値が取れない

#####サンプルテーブル

id name type created_date update_date
1 entry 1 2020-01-08 12:04:56 2020-01-08 12:04:56
2 introduce 1 2020-01-11 14:24:31 2020-01-11 14:24:31
3 introduce 1 2020-01-12 13:29:01 2020-01-08 15:42:21
4 recommend 2 2020-01-15 11:16:30 2020-01-15 11:16:30
5 make 3 2020-01-16 14:54:16 2020-01-16 14:54:16
6 read 3 2020-01-18 13:12:06 2020-01-19 12:02:24

#####SQL

select updated_date from tables group by type order by updated_date desc

##原因
SQLの命令を処理する順番として、GROUP BYが優先されるため
つまり、一旦GROUP BYでグループされた後に昇順・降順を決定するため、意図しない値が取得されていたという訳。

##対処方法
SELECTで取得する項目が一つの場合、MAX関数を使用する

select MAX(updated_date) from tables group by type;

SELECTで取得する項目が複数の場合、サブクエリを構築する

select * from tables where updated_date in (select MAX(updated_date) from tables group by type);
3
3
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?