Re: Deleting all but one row of a list of non-uniques

On Wed, Jun 23, 2004 at 02:17:50AM +0200, Zak McGregor wrote:
> Hi all
>
>
> I have a table, for simplicity's sake containing one field, called unid.

<snip>

> (I've marked the rows with duplicated unids with the * to the right)
>
> I'd like to delete rows in such a way that one (and any one) row for each unid
> remains, and all other duplicates are removed. Does anyone have any ideas that
> may help me here please?

The usual scheme I use is something like: (not perfect SQL)

delete from table where exists (select from table where this.id <
that.id).

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.