条件付の重複レコードの削除 (sqlite3)

質問

重複レコードの削除で、重複レコード内の日付が最も新しいのだけを残したいのですが
sqlite3のクエリをどう書けるのでしょうか？

例えば以下のような構造で、keyの重複を削除するのですが、
keyごとにyear, monthのもっとも新しいのだけ残したのです。
よろしくお願いします。

id, key, year, month, value
1 , aaa, 2000,12, 1000
2 , bbb, 2001, 1, 600
3 , bbb, 2001, 4, 500
4 , bbb, 2000, 2, 400
5, ccc, 2001, 3, 300
6, ccc, 2001, 8, 200

クエリ実行の結果こうしたい↓
1 , aaa, 2000,12, 1000
3 , bbb, 2001, 4, 500
6 , ccc, 2001, 8, 200

回答No.2

mpro-gram · ベストアンサー

削除対象テーブルとの相関クエリがだめなのかな？先にid のリストが確定すれば大丈夫かな？
delete from keys
where id not in ( select id from keys t0
where exists ( select * from
(select key, max( year*100+ month ) as max_ym from keys group by key
) as t1
where t0.key=t1.key and t0.year = round( t1.max_ym / 100 ) and t0.month=t1.max_ym % 100
)
);

お礼

遅れました。
ありがとうございます。これで行きました！
すばらしいです＾＾

補足

keyグループごとに最大値を振って、
条件に合わないものを消してる感じになるのですかね。）
条件の解釈が難しいですね。

回答No.1

mpro-gram

まずは残す行を見つけるselect文が出来れば、 where id not in () とか where not exists () で、不要なid行を削除できる。
delete from `tbl` t0
where not exists ( select * from
(select key, max( year*100+ month ) as max_ym from `tbl` group by key
) as t1
where t0.key=t1.key and t0.year = round( t1.max_ym / 100 ) and t0.month=t1.max_ym % 100
) ;
実行チェックはしてないけど、これでどうかな？

補足

Error: near "t0": syntax errorです。以下のように入力しました。table name はkeysです。t0をkeysにしてもだめですね。うーん。

delete from keys t0 where not exists (select * from (select key,max(year*100+month) as max_ym from keys group by key) as t1 where t0.key = t1.key and t0.year=round(t1.max_ym/100) and t0.month=t1.max_ym % 100;