仕事や面接の多くの質問は、同じ形をしています:各カテゴリーで最も売れた製品、各顧客の最新の注文、各部署で最も給与の高い2人などです。GROUP BYではこれらに答えられません。MAXだけでなく、行全体が必要だからです。
パターン:CTEの中で順位を付け、その外側で順位によって絞り込みます。
WITH ranked AS (
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE rn = 1;
WHEREはウィンドウ関数より前に実行されるため、CTE(またはサブクエリ)が必要です。WHERE ROW_NUMBER() OVER (...) = 1はエラーになります。
順位付けの関数は意図して選んでください。ROW_NUMBER()は、同順位を任意に決めて、グループごとにちょうどN行を返します。RANK()とDENSE_RANK()は、境界で同順位になったすべての行を残します。給与の高い人を問うような質問では、通常こちらのほうが公平です。Snowflake、BigQuery、DuckDBには、ウィンドウ関数で直接絞り込めるQUALIFYもありますが、PostgreSQL、MySQL、SQL Serverにはありません。