フィールドの日付が最新のレコード、フィールドが最大値のレコードを取得するSQL - NOT EXISTS の利用 - SQL

フィールドの日付が最新のレコード、フィールドが最大値のレコードを取得するSQLを紹介します。

概要

テーブルのレコード内で、フィールドの日付が最新のレコードやフィールドの値が最大値のレコードを取得したい場合があります。 この記事では、NOT EXISTS を利用して、フィールドの値が最大値のレコードを取得するSQLを紹介します。

例1:値の最大値のレコードを求める例

以下のテーブルを用意します。

Products

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
2	X-XD05	ラージデスク	オフィス	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	ホーム	チェア	28000
4	O-XX100	ナチュラルオフィス	オフィス	チェア	13800
5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	R7000	ウッドキャビネット	オフィス	その他	32000
7	B-200	リネンベッド	ホーム	ベッド	184500
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
9	W-80	ワーキングチェア	オフィス	チェア	45000
10	EG-10X	エルゴノミクスデスク	オフィス	テーブル	88500

チェアで最もprice(価格)の高いレコードを求めます。

ORDER BYを利用する方法

シンプルな方法では次のコードで求められます。
priceの値で降順にソートし、一番最初のレコードを取得すれば、priceの大きいレコードになります。

select top(1) * from Products as m where category='チェア' order by price desc

MAXを利用する方法

別の方法では、MAX関数を利用する方法もあります。

select * from Products where price = (SELECT MAX(price) FROM Products where category='チェア') and category='チェア'

SELECT MAX(price) FROM ProductsB where category='チェア' のサブクエリで、チェアのカテゴリで価格の最大値を求めます。 価格の最大値と一致するレコードをテーブルから検索します。 このクエリの場合、他のカテゴリに同じ価格のレコードがある場合に結果に含まれてしまうため、メインのクエリでもcategory='チェア' の条件を追加して、 他のカテゴリのレコードが含まれないよう制限します。

NOT EXISTSを利用する方法

別の方法では、NOT EXISTSを利用する方法があります。以下のSQLを実行します。

select * from Products as P where category='チェア' and not exists (select * from Products as Q where P.category=Q.category and P.price < Q.price)

select * from Products as Q where P.category=Q.category and P.price < Q.price では、Productsテーブルのレコードで カテゴリが同じもので、 レコードの値より大きいレコードを検索します。price が最大値のレコード以外では大きいレコードが存在し、price が最大値のレコードは検索結果が無いため、 NOT EXISTSを記述して、レコードがない場合に、そのレコードを結果とする動作にします。
EXISTSの動作の詳細についてはこちらの記事を参照してください。

実行結果は以下になります。

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000

NOT EXISTS を利用するメリット

単純に最大値のレコードを求める場合は、ソートによる方法を利用し、NOT EXISTSを利用する必要はありませんが、 グループごとに最大値のレコードを求める場合にはNOT EXISTSを利用した記述を利用する必要があります。 次のSQL文は、categoryごとのpriceの最大値のレコードを選択します。

select * from Products as P where not exists (select * from Products as Q where P.category=Q.category and P.price < Q.price)

実行結果は以下になります。
カテゴリごとにpriceの最大値のレコードを選択できました。

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	R7000	ウッドキャビネット	オフィス	その他	32000
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850

同じ値が存在する場合の動作

テーブルにレコードを追加して下記の状態にします。チェアでPriceの最大値のレコードを2つにしています。

Products

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
2	X-XD05	ラージデスク	オフィス	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	ホーム	チェア	28000
4	O-XX100	ナチュラルオフィス	オフィス	チェア	13800
5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	R7000	ウッドキャビネット	オフィス	その他	32000
7	B-200	リネンベッド	ホーム	ベッド	184500
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
9	W-80	ワーキングチェア	オフィス	チェア	45000
10	EG-10X	エルゴノミクスデスク	オフィス	テーブル	88500
11	OC-90	オーガニックチェア	ホーム	チェア	56000

上記のテーブルで、以下のSQLを実行します。

select * from Products as P where category='チェア' and not exists (select * from Products as Q where P.category=Q.category and P.price < Q.price)

SQLの実行結果は以下になります。Priceの最大値のレコード2つが結果として取得できます。

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
11	OC-90	オーガニックチェア	ホーム	チェア	56000

すべてのカテゴリの最大値を求める場合のSQLを実行した場合の結果は次の通りです。

select * from Products as P where not exists (select * from Products as Q where P.category=Q.category and P.price < Q.price)

実行結果は以下になります。
カテゴリごとにpriceの最大値のレコードを選択できます。最大値のレコードが複数ある場合は、複数のレコードが選択されます。

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	R7000	ウッドキャビネット	オフィス	その他	32000
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
11	OC-90	オーガニックチェア	ホーム	チェア	56000

補足: 別の方法

GROUP BY と INNER JOIN を利用する方法

group by でcategoryの種類の一覧を作成し、max(price)でカテゴリの最大のpriceを求めます。元のテーブルと priceの最大値とcategoryで inner join すると、priceの最大値のレコードを取得できます。理解しやすいロジックですが、idではなく、 値でjoinするため、joinのパフォーマンス面で若干心もとない面があります。

select * from ProductsB
 inner join (select category, max(price) as price from Products group by category) as P
 on Products.category=P.category and Products.price=P.price

PARTITION BY を利用する方法

partition by を利用する方法もあります。 partition by の詳細はこちらの記事を参照してください。

 select * from(
 select model, category,name, price, max(price) over(partition by category) as ra from Products
 ) as a where a.price = a.ra

以下のSQLはpriceで降順にソートし、rank()関数で順位の数値を割り当て、値が"1"のものを取り出す方法です。

 select * from(
  select model, category,name, price, rank() over(partition by category order by price desc) as ra from Products
) as a where a.ra = 1

例2:日付の最大値のレコードを求める例

以下のテーブルを用意します。

ExamScore

id	date	name	category	score
1	2022-04-16	ぺんた	国語	88
2	2022-04-16	ぺんみ	国語	90
3	2022-04-17	ぺんた	算数	75
4	2022-04-17	ぺんみ	算数	94
5	2022-08-13	ぺんた	国語	76
6	2022-08-13	ぺんみ	国語	85
7	2022-08-14	ぺんた	算数	64
8	2022-08-14	ぺんみ	算数	92
9	2022-12-10	ぺんた	国語	85
10	2022-12-10	ぺんみ	国語	78
11	2022-12-11	ぺんた	算数	94
12	2022-12-11	ぺんみ	算数	82

各個人のそれぞれのカテゴリで最新の日付のレコードを取得します。
以下のSQLを実行します。

select * from ExamScore as P where not exists (select id from ExamScore as Q where P.name=Q.name and P.category=Q.category and P.date < Q.date)

実行結果は以下になります。nameごと、categoryごとに最新の日付のレコードが取得できます。

id	date	name	category	score
9	2022-12-10	ぺんた	国語	85
10	2022-12-10	ぺんみ	国語	78
11	2022-12-11	ぺんた	算数	94
12	2022-12-11	ぺんみ	算数	82

なお、nameごとに最新の日付のレコードを取得する場合は次のSQL文になります。

select * from ExamScore as P where not exists (select id from ExamScore as Q where P.name=Q.name and P.date < Q.date)

実行結果は以下になります。

id	date	name	category	score
11	2022-12-11	ぺんた	算数	94
12	2022-12-11	ぺんみ	算数	82