SQLでMax値を含む行を抽出する方法

Max値を含む行を抽出する方法

やりたいこと

例えば、次のようなケースを想定する。
社員名簿には、社員名・社員の所属部署・社員の給料のデータが含まれる。
所属部署ごとに、給料が最大の社員の行を抽出したい。

下記のテーブル(employee)を想定する。

id	Name	Department	Salary
1	Tanaka	Sales	500
2	Suzuki	Sales	600
3	Yamada	Sales	1000
4	Nakano	Accounting	650
5	Inoue	Accounting	550
6	Nomura	Accounting	450
7	Nakajima	Research	900
8	Sugiyama	Research	700
9	Hayashi	Research	1100

実行環境

PostgreSQL
pgAdmin4

Max行取得の実現方法

1.Max値を特定する-集約関数/分析関数の利用
2.特定したMax値を含む行を抽出する-JOINの利用

1.Max値を特定する-集約関数/分析関数の利用
まずは、各部署ごとの給料の最大値を特定する。
集約関数を利用する場合、group byで所属ごとに集約し、Maxの給料を表示する。

MaxSalayVal_group.sql

select Department, Max(salary) as MaxSalary
    from Employee
    group by Department 

表示結果

Department	MaxSalary
Accounting	650
Research	1100
Sales	1000

分析関数(window関数)を利用しても、partition byで所属ごとのmaxの給料を表示できる。

MaxSalayVal_window.sql

select id, Name, Department, Max(salary) over(partition by department) as MaxSalary
    from Employee

表示結果

id	Name	Department	MaxSalary
4	Nakano	Accounting	650
6	Nomura	Accounting	650
5	Inoue	Accounting	650
9	Hayashi	Research	1100
7	Nakajima	Research	1100
8	Sugiyama	Research	1100
1	Tanaka	Sales	1000
3	Yamada	Sales	1000
2	Suzuki	Sales	1000

2.特定したMax値を含む行を抽出する-JOINの利用
employeeテーブルと、1で作成したテーブルをjoinで結合する。
集約関数を利用した場合。

MaxSalayRow_group.sql

select emp_1.id, emp_1.Name, emp_1.Department, emp_1.Salary
    from employee as emp_1
    inner join
        (select Department, Max(salary) as MaxSalary
            from Employee
            group by Department )
        as emp_2
        on emp_1.Department = emp_2.Department and emp_1.Salary = emp_2.MaxSalary

表示結果

id	Name	Department	Salary
3	Yamada	Sales	1000
4	Nakano	Accounting	650
9	Hayashi	Research	1100

分析関数を利用した場合。

MaxSalayRow_window.sql

select emp_1.id, emp_1.Name, emp_1.Department, emp_1.Salary
    from employee as emp_1
    inner join
        (select id, Max(salary) over(partition by department) as MaxSalary
            from Employee)
        as emp_2
        on emp_1.id = emp_2.id and emp_1.Salary = emp_2.MaxSalary

表示結果

id	Name	Department	Salary
4	Nakano	Accounting	650
9	Hayashi	Research	1100
3	Yamada	Sales	1000

分析関数を用いると、on句でidが同じことも結合条件に加えられるので、分析関数を用いた方が良いですね。
(今回は問題ないけど、idで結合条件を書けない集約関数を用いた場合、うまくいかないことがある。)