各グループの中から、特定条件に合致するレコードを抽出する
例えば以下のようなテーブル「users」があるとして。
id | type | name | age |
1 | 1 | Alice | 17 |
2 | 1 | Billy | 20 |
3 | 2 | Cathy | 38 |
4 | 2 | David | 59 |
5 | 2 | Elena | 27 |
typeごとにageの最大値を求めるのは簡単です。
type | age |
1 | 20 |
2 | 59 |
SELECT "type", MAX("age") AS "age" FROM "users" GROUP BY "type"
でも、typeごとにageが最大のレコードについて、他の情報も欲しいとしたら。
id | type | name | age |
2 | 1 | Billy | 20 |
4 | 2 | David | 59 |
SELECT "users"."id", "users"."type", "users"."name", "users"."age" FROM "users" INNER JOIN ( SELECT "type", MAX("age") AS "age" FROM "users" GROUP BY "type" ) AS "users_tmp" ON "users"."type" = "users_tmp"."type" AND "users"."age" = "users_tmp"."age"
サブクエリを使ってこんな感じで実現は可能…ですが…
いまいちスマートじゃなくてぐぬぬ。
でもここでひらめいた。
PostgreSQL、配列扱えるじゃん。
ということで、以下のようなのもアリか。
SELECT (array_agg("id" ORDER BY "age" DESC))[1] AS "id" ,"type" ,(array_agg("name" ORDER BY "age" DESC))[1] AS "name" ,MAX("age") AS "age" FROM "users" GROUP BY "type"
うわーMySQLではやれない芸当。きもい。
スマートかと言われると首をひねるけど、GROUP一発で一応実現できるのは面白い。
しかしそもそもこれで困るのはDB設計が間違ってるんじゃないか?