グループごとに任意のフィールドの最大の値を持つレコードを抽出し、さらに最大値が同じものが複数存在した場合は、IDが若いレコードを選択したい。

Question

たとえば下記のようなレコードを持ったテーブルがあったとします。

ID |GROUP|PARAM
1 | A | 60
2 | A | 70
3 | A | 80
4 | B | 50
5 | B | 80
6 | B | 80
7 | C | 60
8 | C | 50
9 | C | 20

ここから下記のようにグループごとに最大の値を持つレコードを抽出したいです。

3 | A | 80
5 | B | 80
7 | C | 60

ただし、Bグループのように、最大値となるレコードが複数存在した場合、IDの小さい方を
選択する、といったSQLが書きたいのですが、どのように記述を行えばいいでしょうか？
SQLServerです。

Answer 1

SQLServerの分析関数の使用例の「5. 順位を付ける」
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html#1-5
を見ながら、Oracle11gR2で作ってみました :-)

with t(ID,ColGroup,PARAM) as(
select 1,'A',60 from dual union
select 2,'A',70 from dual union
select 3,'A',80 from dual union
select 4,'B',50 from dual union
select 5,'B',80 from dual union
select 6,'B',80 from dual union
select 7,'C',60 from dual union
select 8,'C',50 from dual union
select 9,'C',20 from dual),
tmp as(
select ID,ColGroup,PARAM,
Row_Number() over(partition by ColGroup order by PARAM desc,ID) as rn
  from t)
select ID,ColGroup,PARAM
  from tmp
 where rn = 1
order by ColGroup;

ID  ColGroup  PARAM
--  --------  -----
 3  A            80
 5  B            80
 7  C            60

Answer 2

定番の相関サブクエリを使うパターンだと思います。

select
  id, "GROUP", PARAM
from t
where
  ID = (
    select top 1 x.ID
    from t as x
    where x."GROUP" = t."GROUP"
    order by x.PARAM desc, x.ID asc
)

http://sqlfiddle.com/#!6/cce11e/7

---

列がその３つだけならこんなかんじでもできるかも。

select
  min(t.id) as ID, t."GROUP", t.PARAM
from t inner join t as x
  on x."GROUP" = t."GROUP"
group by
  t."GROUP", t.PARAM
having
  t.PARAM = max(x.PARAM)
order by
  ID asc

http://sqlfiddle.com/#!6/cce11e/9

Comments

[a-takahashi]

大変参考になりました。
おかげで解決することができました！

Answer 3

SELECT MIN(T.ID) ID, T.GRP, T.PARAM FROM TEST T
INNER JOIN (SELECT GRP, MAX(PARAM) PARAM FROM TEST GROUP BY GRP) T2
on T2.GRP = T.GRP AND T2.PARAM = T.PARAM
GROUP BY T.GRP

SQLSeverでテストしていませんが。

GRP毎の最大のPARAMを求めるその中のIDの最小を出す。
こんな感じでしょうか。

Answer 4

あまりしっかり検証していませんが、以下のクエリでどうでしょうか。

1.まずはグループ毎の最大値を取得。
2.最大値を持つレコードのうち、IDが一番若いものを取得。

select
	  min(B.[ID]) as [ID]
	, A.[GROUP]
	, A.[MAX_PARAM]
from (
	select
		  [GROUP]
		, max([PARAM]) as [MAX_PARAM]
	from @Table
	group by [GROUP]
) as A
inner join @Table as B
	on A.[GROUP] = B.[GROUP] and A.[MAX_PARAM] = B.[PARAM]
group by A.[GROUP], A.[MAX_PARAM]