SQLの書き方

以下の条件を取得したいのですがどのようなSQLを組めばいいか悩んでます。

テーブルA(列はC1,C2,C3,C4 PKはC1,C2です。）

■テーブルAのデータ
C1　　C2　　　C3　　　C4
1 　　1 　　50 　　備考1です。
1 　　2 　　75 　　備考2です。
2 　　1 　　25 　　備考3です。
3 　　1 　　75 　　備考4です。
3 　　2 　　90 　　備考5です。
3 　　3 　　50 　　備考6です。

■取得したい結果
C1　　C2　　　C3　　　C4
1 　　2 　　75 　　備考2です。
2 　　1 　　25 　　備考3です。
3 　　2 　　90 　　備考5です。

■取得したい条件
C1でグルーピングしたなかでC3の値が一番大きな行を取得する。


現在は以下のようなSQLを書いています。
SELCT T1.*
FROM テーブルA T1,
(SELECT C1,MAX(C3) AS C3MAX FROM テーブルA　GROUP BY C1) T2
WHERE T1.C1 = T2.C1
AND T1.C3 = T2.C3MAX

もう少し簡単なSQLで出来そうですが、うまく思いつきません。
宜しくお願いします。

No.4 ベストアンサー 回答者： 3rd_001 回答日時： 2009/02/26 18:42

掲示板を勘違いしてOracleで作成していました。

create table a
(
c1 int,
c2 int,
c3 int,
c4 nvarchar(50)
);

select x.C1,x.C2,x.C3,x.C4
from (
select C1,C2,C3,C4,max(C3) over(partition by C1) max_c3
from a) AS x
where x.C3 = x.max_c3;

SQL Serverだとこっち。

この回答へのお礼

ご回答ありがとうございます。
対象のテーブルが１２０万件以上あるのでパフォーマンスを考慮したSQLだととても助かります。
試してみたいと思います。

お礼日時：2009/02/27 19:28

No.3 回答者： 3rd_001 回答日時： 2009/02/26 18:35

あえていうなら分析関数を使う。

テーブルAのFullスキャン2回を1回に減らせるからデータ量が多いと効果があるかもしれません。

create table a
(
c1 number(2),
c2 number(2),
c3 number(2),
c4 varchar2(50)
);
insert into a values(1,1,50,'備考1です。');
insert into a values(1,2,75,'備考2です。');
insert into a values(2,1,25,'備考3です。');
insert into a values(3,1,75,'備考4です。');
insert into a values(3,2,90,'備考5です。');
insert into a values(3,3,50,'備考6です。');


select C1,C2,C3,C4
from (
select C1,C2,C3,C4,max(C3) over(partition by C1) max_c3
from a)
where C3 = max_c3

C1 C2 C3 C4
---------- ---------- ---------- -----------
1 2 75 備考2です。
2 1 25 備考3です。
3 2 90 備考5です。
実行計画
----------------------------------------------------------
Plan hash value: 2104940997

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 474 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 6 | 474 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 6 | 396 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 6 | 396 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C3"="MAX_C3")

Note
-----
- dynamic sampling used for this statement


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

select T1.*
FROM A T1,(SELECT C1,MAX(C3) AS C3MAX FROM A GROUP BY C1) T2
WHERE T1.C1 = T2.C1
AND T1.C3 = T2.C3MAX;

実行計画
----------------------------------------------------------
Plan hash value: 494933846

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 92 | 8 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 6 | 396 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 6 | 156 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| A | 6 | 156 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."C1"="T2"."C1" AND "T1"."C3"="T2"."C3MAX")

Note
-----
- dynamic sampling used for this statement


統計
----------------------------------------------------------
44 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
3 rows processed

No.2 回答者： noah7150 回答日時： 2009/02/26 14:43

現状のSQLでは一番単純な方法と思います

WHERE句で最大値の行をなんて指定できるようになると違うのでしょうが…

でも、この書き方はOracle風ですね
SQL Serverなら
SELCT T1.*
FROM テーブルA AS T1
INNER JOIN(SELECT C1,MAX(C3) AS C3MAX FROM テーブルA　GROUP BY C1) AS T2
ON T1.C1 = T2.C1
AND T1.C3 = T2.C3MAX

かな？
今はOracleでもJOIN記述も出来ますが(笑)

この回答へのお礼

>WHERE句で最大値の行をなんて指定できるようになると違うのでしょうが…
そのようなことができないかなと思い投稿した次第です。

現状できないようですね・・・。

今のSQLで構築しようと思います。ありがとうございました。

お礼日時：2009/02/26 14:59

No.1 回答者： Splatter 回答日時： 2009/02/26 14:06

現在のSQLで問題ないように思われます。

この回答へのお礼

今のSQLで問題ないようですね。
ありがとうございました。

お礼日時：2009/02/26 15:00