On 22 December 2011 15:08, Paul Sanderson <sandersonforens...@gmail.com> wrote: > Hi I have a large table with some duplicate rows that I want to > delete. Essentially I have two columns, one containing a date and one > containing a number. The number column can contain duplicates. For any > row containing duplicate values I want to remove all rows bar the > oldest. > > i.e. if the data is > > 1 1/1/2011 > 2 1/1/2011 > 2 2/1/2011 > 3 1/1/2011 > 3 2/1/2011 > 3 3/1/2011 > 5 7/1/2011 > > I want to be left with > > 1 1/1/2011 > 2 1/1/2011 > 3 1/1/2011 > 5 7/1/2011
CREATE TABLE t( num integer, date text ); INSERT INTO "t" VALUES(1,'1/1/2011'); INSERT INTO "t" VALUES(2,'1/1/2011'); INSERT INTO "t" VALUES(2,'2/1/2011'); INSERT INTO "t" VALUES(3,'1/1/2011'); INSERT INTO "t" VALUES(3,'2/1/2011'); INSERT INTO "t" VALUES(3,'3/1/2011'); INSERT INTO "t" VALUES(5,'7/1/2011'); 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 ); 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 ); > Paul Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users