On 22 December 2011 15:08, Paul Sanderson <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
