On 12/22/2011 11:00 AM, Simon Davies wrote:
DELETE FROM t WHERE rowid NOT IN (
SELECT rowid FROM
( SELECT rowid, num, date FROM t ) CROSS JOIN
( SELECT num min_num, min( date ) min_date FROM t GROUP BY num ) ON
num=min_num AND date=min_date
);
Seems a bit complicated. How about
DELETE FROM t WHERE rowid !=
(SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date LIMIT 1);
If there can be duplicate dates on num, then make it:
DELETE FROM t WHERE rowid NOT IN(
SELECT rid FROM
( SELECT MIN( rowid ) rid, num, date FROM t GROUP BY num, date ) CROSS JOIN
( SELECT num min_num, MIN( date ) min_date FROM t GROUP BY num ) ON
num=min_num AND date=min_date
);
Or
DELETE FROM t WHERE rowid !=
(SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date,
t2.rowid LIMIT 1);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
