【SQL】 ある条件の下でMAX値を取得したい

Question

SQL文で困っております。

下記のようなデータがあった場合に、
IDごとにデータをそれぞれチェックし、
購入日の最大値が2017年12月31日より前の購入日のデータを取得したい場合、
どのようなSQL文で取得できるのでしょうか。
色々と試しているのですが、
ID:1は本来は取得対象外にしたいのですが、
2017年5月7日のデータを取得してしまうのです。

<検索対象データ>
テーブル名：購入履歴テーブル

フィールド名：
ID　　　　購入日

1 2018年4月9日
1 2017年5月7日
1 2016年4月11日
2 2015年6月10日
2 2014年4月9日
2 2013年4月11日
3 2017年5月6日
3 2016年4月7日
3 2014年8月11日

<自分で書いたSQL文で取得したデータ>
ID　　　　購入日

1 2017年5月7日（※取得したくない）
2 2015年6月10日
3 2017年5月6日

<取得したいデータ>
ID　　　　購入日

2 2015年6月10日
3 2017年5月6日
宜しくお願いします。

上記の内容では不足しておりましたので、追記いたします。
自分で書いたSQL文は以下になります。

CREATE TABLE [購入履歴] (
	[ID] [int] NOT NULL PRIMARY KEY,
	[BuyDate] [varchar(14)] [NOT NULL]
	);

SELECT ID, MAX(購入日) AS 購入日
FROM 購入履歴テーブル
GROUP BY ID
HAVING 購入日 < '2017-12-31'

Comments

[m.ts10806]

ご自身で書かれたSQLを提示してください。

[m.ts10806]

また、テーブル定義をご提示ください。それぞれのカラムの型によってやり方が決まってきます。

[sazi]

DBMSは何ですか？分析関数に対応しているDBMSだと効率が良さそうです。

[sazi]

「ID=1は取得したくない」とありますが、条件は「ID=1を除く」ですか？

[matsutake]

saziさん。わかりにくい説明で申し訳ございません。結果的に今回の場合ですと、IDが1のデータは取得しないという意味で記載させて頂いたつもりでした。

[sazi]

「購入日が2017年12月31日以降のデータを含む、IDを除く」ということでしょうか？また、DBMSをタグなりで明確にして下さい。

[matsutake]

saziさん。ご指摘の通りです。申し訳ござませんでした。

[sazi]

謝る必要は無いですよ。訂正すればいいだけですし。で、DBMS（oracleだとかMySQLだとか）の明示もお願いしますね。

[matsutake]

ご丁寧にありがとうございます。SQL Serverになります。

Answer 1

こんなかな？

SELECT ID, MAX(購入日) AS 最終購入日
FROM 購入履歴テーブル
GROUP BY ID
HAVING MAX(購入日) < '2017-12-31'

Comments

[matsutake]

hihijijiさん。
ありがとうございました。
HAVING句内の部分でMAXがない状態で「購入日」と書いており、
更にその「購入日」をGROUP BY句に入れてしまっていた結果、
MAX値が出ないで指定した日付より以前のデータが
全て抽出されるようなことになっておりました。
本当に助かりました。
ありがとうございました。

Answer 2

こんなかんじで

• 元データ

create table tbl(ID int,d date);
insert into tbl values
(1,'2018-04-09'),
(1,'2017-05-07'),
(1,'2016-04-11'),
(2,'2015-06-10'),
(2,'2014-04-09'),
(2,'2013-04-11'),
(3,'2017-05-06'),
(3,'2016-04-07'),
(3,'2014-08-11'); 

• 検索

select ID,max(d) from tbl as t1
where not exists(
select 1 from tbl as t2
group by ID
having max(d)>'2017-12-31'
and t1.ID=t2.ID
)
group by ID

※結局havingだけでやる方がよさそうですが、上記サブクエリを効率化するとこう

select ID,max(d) from tbl as t1
where not exists(
select 1 from tbl as t2
where d>'2017-12-31'
and t1.ID=t2.ID
)
group by ID

Comments

[yambejp]

あ、たしかにhavingを2段階にする意味はなかったですね
select ID,max(d) as max_d from tbl
group by ID
having max_d<'2017-12-31'

[matsutake]

yambejpさん。
私の説明が悪く、うまく伝わっていないかもしれません。
検索のSQL文の「select 1 from tb1 as t2」という部分の「1」はIDの1を指して折るのでしょうか。
ID=1のデータは結果的に購入履歴の最大値が条件で指定した2017年12月31日よりも後の日付のため取得したくないという意味なのです。
説明が悪く、申し訳ございません。

[yambejp]

> 「1」はIDの1を指して折るのでしょうか。

where exists(もしうはwhere not extists)構文はちょっとなれないと分かりづらいですよね
select 1の「1」はダミーで慣用的につかわれる表現です
これは1でも0でもnullでもなんでもよく、
実際にはマッチングしているだけです

[sazi]

not exists中のgroup byは不要ですね。

[matsutake]

なるほど。
まだまだ勉強不足でした。
ご丁寧なご対応、本当にありがとうございました。
尚、教えて頂きましたSQL文の中で「having max_d<'2017-12-31'」の部分ですが、
「having max(d)<'2017-12-31'」と記載しないと「max_dは無効です」とエラーが出てしまい、正常に動かなかったのですが、何か私の書き方が不味かったのでしょうか。
色々と申し訳ございません。

[yambejp]

ああ、すみません
mysqlではselect句で定義したエイリアスを
havingで利用できるのでそう書いてました
SQL Serverでは利用できないのでしょうかね

[yambejp]

TO:saziさん

ご指摘ありがとうございます。
existsですからgroup byする意味なかったですね

Answer 3

どんなテーブルなのかはCREATE TABLEで簡潔に提示できます。
どんなデータがあるかもINSERT文で提示しましょう。

<自分で書いたSQL文で取得したデータ>

も提示するのが掲示板ではマナーです。

Oracleですと、

CREATE TABLE KOKYU_RIREKI
    (
    ID      NUMBER(1) NOT NULL
  , KONYUBI DATE      NOT NULL
  , CONSTRAINT PK_KOKYU_RIREKI PRIMARY KEY(ID, KONYUBI) USING INDEX
    ) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(1, TO_DATE('2018/04/09', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(1, TO_DATE('2017/05/07', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(1, TO_DATE('2016/04/11', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(2, TO_DATE('2015/06/10', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(2, TO_DATE('2014/04/09', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(2, TO_DATE('2013/04/11', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(3, TO_DATE('2017/05/06', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(3, TO_DATE('2016/04/07', 'YYYY/MM/DD')) ;
INSERT INTO KOKYU_RIREKI(ID, KONYUBI) VALUES(3, TO_DATE('2014/06/11', 'YYYY/MM/DD')) ;
COMMIT ;

SELECT KR.ID
     , MAX(KR.KONYUBI) AS MAX_KONYUBI
FROM KOKYU_RIREKI KR
WHERE KR.ID IN(2, 3)
GROUP BY KR.ID
ORDER BY KR.ID ;

Comments

[matsutake]

Orlofskyさん。
大変申し訳ございません。
そして、ご指摘ありがとうございます。
追記している間にOrlofskyさんからご回答をいただいてしまいました。
次回からはしっかりマナーを守り、参加させて頂くように致します。

[Orlofsky]

今回からマナーを守っても遅くはありません。SQLはデータベースによって方言が大きいのでどのデータベースかタグで明示しましょう。

[matsutake]

ご指摘通り、今回からということで追記させて頂きました。
それでもわかりづらかったり、見にくかったりしているかもしれません。
色々な面で皆さんにご迷惑をお掛けしているにも拘らず、
ご丁寧にご回答を頂いたり、マナーをお教え頂けたりと、
本当にありがたいことだと改めて思いました。
今後参加させて頂く際は、今回のようなことがないように気をつけたいと思います。
ありがとうございました。