SQL GROUP BY と ORDER BY の順序・使い分け
実務で頻出する集計クエリは、句の順序と選択列の制約を押さえれば迷いません。本稿では 書き順・評価順、SELECT に書ける列の制約、 PostgreSQL / MySQL / BigQuery の挙動差をまとめます。
A concise reference for the write-order, evaluation-order, SELECT-column constraints, and dialect differences between GROUP BY and ORDER BY.
TL;DR
- 書き順:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT - 評価順:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT SELECTに書ける非集計列はGROUP BYに含まれる列のみ（MySQL以外）ORDER BYは SELECT エイリアスや 1,2... の列番号で指定可
1. 典型クエリ / Canonical query
SELECT user_id,
COUNT(*) AS orders,
SUM(amount) AS total
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id
HAVING SUM(amount) >= 10000
ORDER BY total DESC
LIMIT 50;WHERE は行フィルタ、HAVING は集計後フィルタ。混同すると パフォーマンス劣化や意図しない結果になります。
2. SELECT に書ける列の制約 / Column rule
| DB / Dialect | GROUP BY 外の列 | 挙動 |
|---|---|---|
| PostgreSQL | 不可 | column must appear in GROUP BY |
| MySQL (ONLY_FULL_GROUP_BY on) | 不可 | 5.7+ デフォルトで有効 |
| MySQL (off) | 可 | 任意行が返る（非決定的） |
| BigQuery | 不可 | ANY_VALUE() で明示 |
| SQL Server / Oracle | 不可 | ANSI 準拠 |
3. ORDER BY の書き方 / ORDER BY tips
- SELECT で付けたエイリアス（例:
ORDER BY total DESC）は PostgreSQL/MySQL/BigQuery で動作 - 列番号 (
ORDER BY 2 DESC) は可読性が落ちるため本番クエリでは避ける - NULL 順序は DB 依存。明示したい場合は
ORDER BY col NULLS LAST
4. GROUP BY の落とし穴 / Pitfalls
- MySQL の
ONLY_FULL_GROUP_BYを無効化したコードを他DBに移植すると崩れる DISTINCTとGROUP BYはほぼ同等だが、集計列を足す時はGROUP BY一択- 巨大テーブルでは
ORDER BYの前にLIMIT付きサブクエリで削ると劇的に速い
5. English summary
In SQL, GROUP BY is always written before ORDER BY. The full evaluation order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, which also explains why HAVING can reference aggregates but WHEREcannot. In all major dialects except MySQL with ONLY_FULL_GROUP_BY disabled, every non-aggregate column in SELECT must appear in GROUP BY. When porting MySQL code, validate against Postgres or BigQuery early.