ガイド

グループごとに最新1件を取得する (Greatest-N-per-group)

ユーザーごとの最新注文など、グループ内で最新1件を取りたいとき。

問題 — Greatest-N-per-group

「ユーザーごとに最新の注文を1件」「センサーごとに最後の測定値を1件」といったグループ内1件抽出は SQL の定番課題で "Greatest-N-per-group" と呼ばれます。

単純に MAX(created_at) を GROUP BY しただけでは、その最新行に含まれる他の列（注文ID、金額など）は一緒に取れません。下手に書くと性能が致命的に落ちる領域なので、解法を複数知っておく価値があります。

解法1: ROW_NUMBER() — 標準SQL・どのRDBMSでも

もっとも移植性が高い。グループ内で並び替えて番号を振り、rn = 1 を残すだけ。PostgreSQL / MySQL 8 / SQL Server / SQLite 3.25+ で動きます。

インデックスは (user_id, created_at DESC) が最適。全行を番号付けするので、グループ数が多いほど他の解法より不利になることがあります。

sql

SELECT user_id, order_id, amount, created_at
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders o
) t
WHERE rn = 1;

解法2: DISTINCT ON — PostgreSQL なら最短

PostgreSQL 独自拡張。DISTINCT ON (グループキー) + ORDER BY グループキー, 並び順 で、各グループの先頭1行を返します。短くて速い。

ただし PostgreSQL 以外では動きません。移植予定のコードでは避ける、内製の PG 専用アプリでは積極採用、という線引きで。

sql

-- PostgreSQL 専用
SELECT DISTINCT ON (user_id)
  user_id, order_id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;

解法3: LATERAL 結合 — インデックスを活かしやすい

親テーブル（users など）があるとき、そこから子側に LATERAL 結合して LIMIT 1する書き方。ユーザー数が少なく、ユーザーごとに注文が大量、というデータ形状だと (user_id, created_at DESC) インデックスを素直に使って最速になりやすい。

PostgreSQL / SQL Server (CROSS APPLY) で使える。MySQL 8 も LATERAL 対応。

sql

-- PostgreSQL / MySQL 8
SELECT u.id AS user_id, o.order_id, o.amount, o.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT order_id, amount, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) o ON TRUE;

解法4: 相関サブクエリ — 書きやすいが遅くなりがち

WHERE created_at = (SELECT MAX(created_at) FROM orders WHERE user_id = o.user_id) のような書き方。読みやすく一見自然だが、行ごとにサブクエリが走る可能性があり、大量データでは最も遅くなるパターン。

また、同じユーザーで created_at が同値の行が複数あると複数行返ってしまう（TIE 問題）ので、他解法より注意が必要。

選び方まとめ

ポータビリティ重視 → ROW_NUMBER()
PostgreSQL 専用で短く書きたい → DISTINCT ON
親テーブルがあり、グループ少・グループ内行多 → LATERAL
相関サブクエリは小規模データ or 説明用にとどめる