Transact SQL (SQL Server) で GROUP BY クエリーが返す列の値に最大または最新のレコードの値を返すようにしたい (OVER PARTITION BY の利用) - SQL Server

Transact SQL (SQL Server) で GROUP BY クエリーが返す列の値に最新のレコードの値を返すクエリを紹介します。

概要

下図のテーブルを例とします。

ProductsA テーブル

id	name	price	category
1	Penguin	500	Bird
2	Bear	1050	Mammal
3	Duck	150	Bird
4	Camel	550	Mammal
5	Owl	185	Bird
6	Whale	880	Mammal

一般的にSQLでは、group by を利用すると、group by した列以外の列は選択できません。

select category from ProductsA group by category

select category, price from ProductsA group by category

しかし、実際の利用では特定のフィールドが最大値のレコードの指定した列を取り出したいことがあります。
group byを利用して、category でグループ化しmax_name列に、グループの中で一番Priceの高いレコードのnameを表示したいといった場合があります。先のテーブルでの具体例では下表の結果となるクエリです。

category	max_name
Bird	Penguin
Mammal	Bear

対応方法:1

上記の結果を得る場合、group by 句ではなく、over ～ partition by 句を利用すると、求める結果が取得できます。
先に紹介した、ProductsA テーブルの場合、下記のSQLとなります。

select category,name, price, max(price) over(partition by category) from ProductsA

このSQLを実行すると下図の結果が返ります。

category	name	price	(列名なし)
Bird	Penguin	500	500
Bird	Duck	150	500
Bird	Owl	185	500
Mammal	Whale	880	1050
Mammal	Camel	550	1050
Mammal	Bear	1050	1050

余計なレコードが含まれており、期待する結果とは異なりますが、priceと最大値の一致しているレコードを取り出せば目的の結果となります。目的のレコードのみを取り出すため、SQLを下記に変更します。

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

上記のSQLの実行結果は下記になります。求める値が取り出せていることが分かります。

category	name	price	ra
Bird	Penguin	500	500
Mammal	Bear	1050	1050

対応方法:2

上記の方法とほぼ同様ですが、以下のクエリーでも同様の結果が得られます。

select category,name, price, rank() over(partition by category order by price desc) as ra from ProductsA

上記のSQLを実行すると下記の結果が得られます。下記の結果のra=1 のレコードを取り出せば求める結果となることが分かります。

category	name	price	ra
Bird	Penguin	500	1
Bird	Owl	185	2
Bird	Duck	150	3
Mammal	Bear	1050	1
Mammal	Whale	880	2
Mammal	Camel	550	3

ra=1 のレコードのみに絞り込む条件を加え、下記のSQLとします。

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

上記のSQLの実行結果は下記になります。求める値が取り出せていることが分かります。

category	name	price	ra
Bird	Penguin	500	1
Mammal	Bear	1050	1