Re: SQL for Deleting all duplicate entries

Håkan Jacobsson wrote:
> Thanx Merlin, have a nice one (vacation)!
>
> It turns out I have'nt described the problem accurately=(
> Data may actually differ in two of the columns (the varchar
> columns).
> I still want to remove rows which share the same data in those
> two columns and have the date column
> set to NULL.
> I.e. row 1,2,3  have:
>       column1 = 'foo', column2 = 'hey' and the date column =
> NULL
>       row 4,5,6 have:
>       column1 = 'brat', column2 = 'yo' and the date column =
> NULL
> I want to keep just one of the 1 - 3 rows and one of the 4 - 6
> rows..
>
> I will try Merlins and Scotts solutions tomorrow. Anyone know
> if I need to modify Merlins and/or Scotts
> solutions to solve this new situation?
>

If i understand correctly, this should give you the records you want to
keep:

SELECT DISTINCT ON (t.one, t.two) t.one, t.two, t.three, [t.n] FROM foo
AS t;

Put those into a tmp table, truncate the original, then put the saved
rows back in.

brian