Re: Deleting duplicate rows from a table:SQL Command???

AH3141_at_uacsc2.albany.edu writes:

> Is there an SQL command that will delete duplicate rows from a table based on
> the following criteria?
>
> Table1 has two columns: id char(5), id_date date.
>
> delete from table1 where id=id and id_date < id_date
>
> I would like to be left with a unique id with the most current date.

This should work, though it probably won't be very fast for a large table:

DELETE	FROM TABLE1
WHERE	(ID, ID_DATE) IN
	(
	SELECT	ID, ID_DATE
	FROM	TABLE1 X
	WHERE	ID_DATE <
		(
		SELECT	MAX(ID_DATE)
		FROM	TABLE1
		WHERE	ID = X.ID
		)
	)

Or you could also do this, which might be faster (I'm not sure):

DELETE	FROM TABLE1 X
WHERE	EXISTS
	(
	SELECT	1
	FROM	TABLE1
	WHERE	ID_DATE > X.ID_DATE
	AND	ID = X.ID
	)

--------------------------------------------------------------------------------
David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE