SQLでグループごとの最大値の行を取得するには|同順位の扱いも確認

SQLでグループごとの最大値を求めることと、その値を持つ行を取り出すことは別の処理です。MAXだけでは、最大値と同じ行にある識別子や日時を自動的に選べるわけではありません。

ここではウィンドウ関数を使い、同順位をどう扱うかまで整理します。仕様はPostgreSQL 18、掲載例の結果確認はSQLiteのメモリ内DBで行っています。PostgreSQL実機での検証ではありません。

最大の値と対応する行を区別する

仮にAグループに100・200・200、Bグループに50の値があるとします。グループ別のMAXならAは200、Bは50ですが、Aの最大値を持つ行は二件あります。

最大値だけを集計し、関係のない列を無条件でSELECTへ足すと、どの行の値なのかを説明できなくなります。まず「最大値の行をすべて」「同順位でも一件」のどちらが必要か決めます。

一件を選ぶなら順序を最後まで決める

PostgreSQLのウィンドウ関数は、行をまとめて消すことなく、PARTITION BYで区切った範囲内で計算できます。ROW_NUMBERを使う例です。idは一意な番号、amountはNULLを含まない前提とします。

SELECT id, grp, amount
FROM (
  SELECT id, grp, amount,
         ROW_NUMBER() OVER (
           PARTITION BY grp ORDER BY amount DESC, id ASC
         ) AS rn
  FROM scores
) AS ranked
WHERE rn = 1
ORDER BY grp;

同じ最大値がある場合は、小さいidを優先します。この条件は業務上の選択例であり、常に最小idが正解という意味ではありません。最新日時を優先するなら、同日時の扱いも含めて決めます。

同順位をすべて残すならRANKを使う

最大値の行をすべて残す説明例です。

SELECT id, grp, amount
FROM (
  SELECT id, grp, amount,
         RANK() OVER (PARTITION BY grp ORDER BY amount DESC) AS rnk
  FROM scores
) AS ranked
WHERE rnk = 1
ORDER BY grp, id;

順位を付けるORDER BYへ一意なidまで加えると、同じamountの行も同順位でなくなるため、目的に合わせて分けます。外側のORDER BYは結果の表示順を決めるものです。

NULLと同順位の例を用意する

検証では最大値が一件のグループ、最大値が複数のグループ、値の欠損があるグループを分けます。ここに載せた例はNULLなしに限定しているので、欠損を含む実データへ使う際は除外か並び順かを追加で決めます。

どの行を選ぶかの規則を先に決めると、たまたま返った行を正解とする誤りを避けられます。行の識別や表の意味はデータベースの正規化と合わせて整理してください。

記事一覧へ戻る

ITの困りごとを、
まずはご相談ください。

何から手をつけるか決まっていない段階でも構いません。ご相談内容に応じて、担当者からご連絡します。

まずは相談する

採用へのご応募は採用エントリーからお願いします。