SQL Serverでデータを扱う際、特定のグループごとに最大値を持つレコードを取得したい場面があります。例えば、各顧客の最新の注文や、部署ごとの最高給与の社員情報を取得する場合などです。
このようなケースでは、GROUP BY と MAX() を使うだけでは、最大値の「値」は取得できても、その値を持つレコード全体を簡単には取得できません。
本記事では、SQL Serverで「グループごとの最大値を持つレコード全体」を取得する方法について、いくつかのアプローチを紹介します。それぞれの手法のメリット・デメリットも解説するので、用途に応じて最適な方法を選べるようになります。
それでは、実際の方法を見ていきましょう！
JOIN を使った方法
GROUP BY と MAX() では、最大値の「値」は取得できますが、どのレコードがその値を持っているのかまではわかりません。そこで、JOIN を使って、最大値を持つレコード全体を取得する方法を紹介します。
以下のEmployeesテーブルを例に考えてみましょう。
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 1 | Alice | HR | 50000 |
| 2 | Bob | HR | 60000 |
| 3 | Charlie | IT | 70000 |
| 4 | David | IT | 90000 |
| 5 | Eve | Sales | 55000 |
JOIN を使って最大値のレコードを取得
各部署ごとに最高給与を持つ従業員の情報を取得するには、以下のようにJOIN を活用します。
SELECT e.EmployeeID, e.Name, e.Department, e.Salary
FROM Employees e
INNER JOIN (
SELECT Department, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
) max_salaries
ON e.Department = max_salaries.Department AND e.Salary = max_salaries.MaxSalary;実行結果
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 2 | Bob | HR | 60000 |
| 4 | David | IT | 90000 |
| 5 | Eve | Sales | 55000 |
- サブクエリ (
max_salaries) で各部署の最大給与を取得GROUP BY Departmentを使用して、各部署ごとの最大給与を計算
- メインクエリで
INNER JOINを使用Employeesテーブルとmax_salariesを結合し、SalaryがMaxSalaryと一致するレコードのみを取得
この方法はシンプルで分かりやすく、最大値のレコード全体を取得するのに便利です。ただし、同じ最大値を持つ複数のレコードが存在する場合、それらもすべて取得される点に注意が必要です。
次のセクションでは、ROW_NUMBER() を使ってグループごとの最大値のレコードを取得する方法を紹介します。
WITH（CTE）とROW_NUMBER() を使った方法
JOIN を使った方法では、最大値を持つレコードを取得できますが、同じ最大値を持つ複数のレコードが存在する場合、それらがすべて取得されてしまいます。一方、ROW_NUMBER() を使えば、各グループごとに最大値を持つレコードを１件だけ取得することが可能です。
再び、以下のEmployees テーブルを例に考えます。
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 1 | Alice | HR | 50000 |
| 2 | Bob | HR | 60000 |
| 3 | Charlie | IT | 70000 |
| 4 | David | IT | 90000 |
| 5 | Eve | Sales | 55000 |
ROW_NUMBER()を使ってグループごとの最大レコードを取得
以下のSQLで、各部署の最大給与を持つ従業員の情報を１件だけ取得できます。
WITH RankedEmployees AS (
SELECT
EmployeeID,
Name,
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees
)
SELECT EmployeeID, Name, Department, Salary
FROM RankedEmployees
WHERE RowNum = 1;実行結果
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 2 | Bob | HR | 60000 |
| 4 | David | IT | 90000 |
| 5 | Eve | Sales | 55000 |
WITHを使って共通テーブル式（CTE）を作成ROW_NUMBER()を使用して、各DepartmentごとにSalaryの降順で並べ、各行にRowNumを割り当てる
WHERE RowNum = 1で最大値のレコードのみ取得- 各
Departmentについて、RowNum = 1のレコードだけを選択
- 各
✅ メリット
- 各グループごとの最大値を持つレコードを「1 件だけ」取得できる
ORDER BYの条件を変えれば、最小値や他の基準に基づくレコードも簡単に取得可能
⚠️ デメリット
WITH（CTE）を使うため、SQL に慣れていないと少し分かりづらいROW_NUMBER()の計算コストが発生するため、大量データではパフォーマンスに注意
次のセクションでは、CROSS APPLY を使った方法を紹介します！
CROSS APPLY を使った方法
CROSS APPLY を使うことで、各グループごとの最大値を持つレコードを効率よく取得できます。特に、インデックスが適切に設定されている場合、JOIN よりもパフォーマンスが向上することがあります。
再び、以下のEmployees テーブルを使用します。
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 1 | Alice | HR | 50000 |
| 2 | Bob | HR | 60000 |
| 3 | Charlie | IT | 70000 |
| 4 | David | IT | 90000 |
| 5 | Eve | Sales | 55000 |
CROSS APPLY を使って最大値のレコードを取得
SELECT e.EmployeeID, e.Name, e.Department, e.Salary
FROM Departments d
CROSS APPLY (
SELECT TOP 1 EmployeeID, Name, Department, Salary
FROM Employees e
WHERE e.Department = d.Department
ORDER BY Salary DESC
) e;実行結果
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 2 | Bob | HR | 60000 |
| 4 | David | IT | 90000 |
| 5 | Eve | Sales | 55000 |
CROSS APPLYを使用してサブクエリを実行Departmentsテーブルの各レコードに対して、EmployeesテーブルからDepartmentが一致するレコードを取得TOP 1でSalary DESC順に並べ、最大給与のレコードのみを取得
ORDER BY Salary DESCを指定- 各
Department内で給与が最も高い従業員を取得
- 各
✅ メリット
TOP 1により、各グループの最大レコードを効率的に取得CROSS APPLYはJOINよりも柔軟で、特定の条件を追加しやすい- 適切なインデックスを設定すれば、高速に動作
⚠️ デメリット
CROSS APPLYの概念が初心者には少し分かりづらいDepartmentsテーブルが必要（もしEmployeesテーブル単体で使いたい場合は別の工夫が必要）
まとめ
本記事では、SQL Server で「グループごとの最大値を持つレコードを取得する方法」について解説しました。
どの方法を選ぶべきか？
- 「最大値のレコードをすべて取得」したい場合 →
JOIN+MAX() - 「各グループごとに 1 件だけ取得」したい場合 →
ROW_NUMBER() - 「パフォーマンスを考慮しつつ効率的に取得」したい場合 →
CROSS APPLY（インデックス推奨）
SQL の選択肢は多く、データの特性や実行環境によって最適な方法は異なります。用途に応じて適切な方法を選び、パフォーマンスを考慮しながら運用しましょう。
本記事が参考になれば幸いです。ご質問や他の SQL に関する疑問があれば、ぜひコメントでお知らせください！
コメント