SQL構文を手助けしてください

質問

(1)のようなテーブルデータを(2)のように表示したいと思います。

AA,BB,CC,DD,EEは列名としてください。

(1)
AA BB CC DD EE
__________________________
00 01 2004/01/01 XX YY
01 00 2004/01/01 XE YD
01 00 2004/01/05 XZ YZ
02 01 2004/01/10 X2 YE
02 02 2004/01/05 XW YI
02 02 2004/01/06 XF YL

(2)
AA BB CC DD EE
__________________________
00 01 2004/01/01 XX YY
01 00 2004/01/05 XZ YZ
02 01 2004/01/10 X2 YE
02 02 2004/01/06 XF YL

<条件>
AAとBBでキーを作り、CCのMAXの該当レコードを表示したい。
SELECT AA,BB,MAX(CC),DD,EE
FROM TEST_TABLE
GROUP BY AA,BB

というような感じかなと思ったのですが、「GROUP BY の式ではありません」と怒られてしまいます。
どなたか簡単なやり方をご存知で無いでしょうか?

回答No.1

mitoneko · ベストアンサー

そのSELECT文、根本的に、条件に無理があります。条件が足りないというか・・・

　たとえば、元テーブルの、AA,BB = 01,00 の所を例に取りましょう。該当するレコードは、２件あります。CCの最大値は、明確に定義されていますから、簡単です。2004/01/05です。
　ところで、このとき、DD,EEは、いったい何になるんでしょうか？(2)のテーブルでは、XZ,YZとなっていますが、これはなぜですか？なぜ、XE,YDではないのですか？と、まぁこういう事をデータベースは問いかけています。
　そう。DD,EEに対する条件が足りないんです。「GROUP BYの式ではありません。」と言うのは、そういう意味です。
　基本的に、GROUP BYを指定した場合は、SELECTの列リストには、GROUP BYに列挙された式と集合関数しか書けません。文法的に言うとこうなります。

　CCがMAXの列のDDとEEとなると、少々面倒です。
　ここで、新しい条件として、AA,BB,CCの組みは、一意のキーを構成するという条件を付けます。でないと、一つのAA,BB,CCに対して、複数の列が該当すると言うことになり、さっきの話がぶり返しますので。
　わかりやすい方法としては、VIEWを作っちゃうことでしょうか。

CREATE VIEW TEST_TABLE_M (AA,BB,CCM)
AS SELECT AA,BB,MAX(CC) FROM TAST_TABLE
GROUP BY AA,BB

として、VIEWを定義し、このテーブルと結合します。

CREATE AA,BB,CCM,DD,EE
FROM TEST_TABLE A, TEST_TABLE_M M
WHERE A.AA=M.AA AND A.BB=M.BB AND A.CC=M.CCM

といったところでしょうか。
　VIEWを作りたくないのであれば、サブクエリーで条件を書くことになりますが、なんだか、条件が結構、長くなりそうです。「簡単な」とはいいがたくなります。

お礼

回答ありがとうございます。
例題のSQLの矛盾は投稿した後に気付きました。
うん、DD,EE列になにを表示していいかわからないですもんね、、、

VIEWを使えばわかりやすくなりますね。
まぁ結果的に言えばそれほど簡単ではないですね(^^;)
どうもありがとうございました

回答No.2

y_nakkan

下記SQL文は一応動作確認しました。そのまま、エラー無しで使用できると思います。
但し、テーブルの列定義は

create table TEST_TABLE ( AA char(2), BB char(2), CC date, DD char(2), EE char(2) );

と仮定しています。列の型が違う場合、文字列処理部分を修正して下さい。

サブクエリーを使用しないシンプルなSQL文を紹介します。ですので、
レスポンスは早いと思います。
（但し、列DD, EEの列長があまりにも長い場合は、レスポンスが遅くなるかも
しれませんので、その場合は、#1さんの方法や、サブクエリーを使った方法を
試してください。そうでなければ、このSQL文で大丈夫です。）

(SQL1)
SELECT AA, BB, MAX ( to_char( CC, 'yyyy/mm/dd' ) || '*' || DD || '*' || EE )
FROM TEST_TABLE
GROUP BY AA,BB;

MAX()で、同一キーの、列CC, DD, EE を文字連結した文字列の最大値を返します。
日付形である列CCを連結文字の先頭に配置しているため、直近の日付のレコードの
CC, DD, EE を取得できます。　'*' は一応デメリッタです。
プログラム側で、３つ目の列（MAXの列）の値を分解し、それぞれの列の値（CC, DD, EE)を
取得します。

(SQL2)

プログラム側で３つ目の列（MAXの列）の文字列分解処理を行いたくない場合、
SQL1をFROM句のサブクエリーとすることで、文字列分解処理をSQL文側で
行うことも可能です。この場合のサブクエリーはレスポンスに殆ど悪影響を
及ぼしません。

下記では、列DD, EEの値は固定長と仮定していますが、可変長の場合は、
INSTR()でデミリタ '*'を検索し、切り取る位置を動的に処理することも可能です。
また、プログラムから使用することも考慮し、列名 CC, DD, EE も別名をつけて、
アクセスできるようにしています。

select AA, BB, to_date ( substr( max_row, 1, 10 ), 'yyyy/mm/dd' ) CC,
substr( max_row, 12, 2 ) DD, substr( max_row, 15, 2 ) EE
from (
SELECT AA, BB, MAX ( to_char( CC, 'yyyy/mm/dd' ) || '*' || DD || '*' || EE ) max_row
FROM TEST_TABLE
GROUP BY AA,BB);

お礼

デリミタの使用ですか。これまた考えていませんでした。
というか今までそういう考えをしたことがありませんでした。これからも役立ちそうなことを教えていただけたと思います。
また、MAXの中でMAX ( to_char( CC, 'yyyy/mm/dd' ) || '*' || DD || '*' || EE )
という使い方も出来るというのは知りませんでした。
ちょっとビックリしました。

お二方のご助言でなんとかなりそうです
ありがとうございました!