Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQLのグループ内の最大値を抽出するmax関数についてまとめています。

SQLのmaxの構文

SQLのmaxは最大値を抽出する関数です。group byと組み合わせると、グループ内での最大値を抽出します。以下は、departments（部署）ごとの最大の年収（salary）を抽出する例です。

SELECT 
	departments.dept_name as bushomei, 
	max(salary) as max_kyuuryo
FROM 
	salaries 
	left join dept_emp ON dept_emp.emp_no = salaries.emp_no
	left join departments ON departments.dept_no = dept_emp.dept_no
WHERE
	salaries.to_date="9999-01-01"
group by
	bushomei;

+--------------------+-------------+
| bushomei           | max_kyuuryo |
+--------------------+-------------+
| Development        |      144434 |
| Sales              |      158220 |
| Production         |      138273 |
| Human Resources    |      141953 |
| Research           |      130211 |
| Quality Management |      132103 |
| Marketing          |      145128 |
| Customer Service   |      144866 |
| Finance            |      142395 |
+--------------------+-------------+
9 rows in set (3.03 sec)

【関連記事】
▶SQLのgroup byサンプルコード集 count、like、join等の組み合わせ例

maxをサブクエリ内で使用して、WHERE条件に組み込むことも可能です。以下のSQLはsalary（年収）テーブルのto_date（有効期限終了日）が最新（max）ではないレコードを抽出する例です。「最大値以外を抽出」というSQLを、maxを使ったサブクエリとnot inの組み合わせで実現しています。

select * from salaries
where 
	(emp_no, to_date) not in (
		select 
			tmp1.emp_no as emp_no, 
			tmp1.to_date as to_date 
		from 
			(
				select 
					emp_no, 
					max(to_date) as to_date 
				from 
					salaries
				group by 
					emp_no
			) as tmp1
	);

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
 :
 :

【関連記事】
▶SQLで重複を削除するサンプルコード　最新データを残してdeleteするには？ 

Oracleのmaxの構文

Oracleのmax関数の構文です。対象データにNULLが混在していた場合、結果は0になります。NULLしか存在しない場合は、結果がNULLになります。

SELECT MAX(salary) "Maximum"
  FROM employees;
 
   Maximum
----------
     24000

関連）Oracle公式ドキュメント：MAX

group byのパフォーマンスが悪い場合など、overとmaxを組み合わせて使用することも可能です。

SELECT manager_id, last_name, salary,
       MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
  FROM employees
  ORDER BY manager_id, last_name, salary;

MANAGER_ID LAST_NAME                     SALARY    MGR_MAX
---------- ------------------------- ---------- ----------
       100 Cambrault                      11000      17000
       100 De Haan                        17000      17000
       100 Errazuriz                      12000      17000
       100 Fripp                           8200      17000
       100 Hartstein                      13000      17000
       100 Kaufling                        7900      17000
       100 Kochhar                        17000      17000

【関連記事】
▶SQL overは、分析関数 ビッグデータの集計区間を制限しレスポンスを上げる

SQL Serverのmaxの構文

SQL Serverのmaxは、NULLを無視します。NULLしか存在しない場合は、NULLを返します。

SELECT MAX(TaxRate)  
FROM Sales.SalesTaxRate;  
GO  

-------------------  
19.60  
Warning, null value eliminated from aggregate.  
 
(1 row(s) affected)

関連）MAX (Transact-SQL) – SQL Server | Microsoft Docs

MySQLのmaxの構文

MySQLでは、maxの対象にNULLが含まれていても、全て無視されます。NULLしか存在しない場合は、NULLが返されます。

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article;

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.6.3 グループごとのカラムの最大値

PostgreSQLのmax

PostgreSQLでは、maxの対象にNULLが含まれていても、全て無視されます。NULLしか存在しない場合は、NULLが返されます。必要なら、coalesce関数でNULL回避が可能です。

【関連記事】
▶SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;
     city      | max
---------------+-----
 Hayward       |  37
 San Francisco |  46
(2 rows)

関連）PostgreSQLの公式レファレンスマニュアル：集約関数

まとめ

ポテパンダの一言メモ
  • maxは最大値を返すSQLの関数
  • group byやoverと組み合わせて、グループ内の最大値を抽出可能
  • maxの対象にNULLが混在していた場合の扱いは、DBMSごとに異なる

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。