グループ集計で最小(最大)値を持つ行のみ取得するSQL

  • このエントリーをはてなブックマークに追加
  • このエントリーをはてなブックマークに追加

A列をグループとしたときに、B列の最小(最大)値を持つ行だけを取得したいとします。
こんなときは NOT EXISTS を使って最小(最大)値のみに絞り込みます。


例えは、下記のようなテーブルがあるとします。

CREATE TABLE [dbo].[Test](
	[Type] [int] NOT NULL,
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NULL,
	[Value] [int] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
	[Type] ASC,
	[ID] ASC
	) ON [PRIMARY]
) ON [PRIMARY]

このテーブルには下記のようにデータが入力されています。
Type 列をグループとして Value の値が最小値を取得する場合、該当するデータは背景色がグレーのデータとなります。

Type ID Name Value
1 1 AAA1 20
1 2 AAA2 10
2 1 BBB1 30
2 2 BBB2 10
2 3 BBB3 20

実際のSELECT文は下記のような感じです。

SELECT
	*
FROM  Test  A
WHERE  NOT EXISTS  (
	SELECT  *
	FROM  Test
	WHERE  Type=A.Type
	AND  Value<A.Value
)

サブクエリで取得出来るデータは、該当するデータとは反対のデータです。
メインクエリの Type 値とサブクエリの Type 値が等しく(グループとしてる)、サブクエリの Value 値より メインクエリの Value 値が大きいデータとなります。サブクエリの結果を NOT EXISTS としてるのでその反対、最小値のみとなります。

※最大値の場合は、「Value<A.Value」の「小なり(<)」が「大なり(>)」になります。

上記の SQL 文は SQLServer にて確認しました。
他のベンダーDBでもっと簡単に取得できる方法があるならコメント頂けると助かります。

関連記事:

Pocket
Bookmark this on Delicious
Bookmark this on Google Bookmarks

カテゴリー: SQL

グループ集計で最小(最大)値を持つ行のみ取得するSQL” への4件のフィードバック

  1. グループ集計で最小(最大)値を持つ行のみ取得するSQL

    完全にトラックバックのURL紹介になりますが。
    というか、まんまなんですけど、非常に役に立ったので!
    MIYABIS NOTE さん
    タイトルの通り…

  2. そうかもしれませんが、でもすごく遅いような気がします。

    1. これを使ったシステムで遅いという問題は発生してませんが
      テーブル構成やデータ量によっては遅いかもしれません。
      新しいDBなら関数を駆使した方が速いと思われます。

  3. 僅少ですが、こちらのほうがコストが少ないようです。
    select
    B.*
    from dbo.Test B
    inner join (
    select
    A.Type,
    min(A.Value) minval
    from dbo.Test A
    group by A.Type ) C
    on B.Type = C.Type and B.Value = C.minval

    ちなみにOracleだとサブクエリを使わずに書けるみたいですね。
    select
    Type,
    ID,
    Name,
    max(Value) over(partition by Type) as maxValue
    from Test;

コメントを残す

メールアドレスが公開されることはありません。

*

This blog is kept spam free by WP-SpamFree.