Sori

> I have a table with Field1, Field2
> Some records have a equal values on the first 2 fields.
> Rec1.Field1 = Rec2.Field1;
> Rec1.Field2 = Rec2.Field2;
>
> I want to remain only with the first record and delete the others.

I was going to suggest

DELETE FROM Table WHERE Id NOT IN
(SELECT min(Id) FROM Table
GROUP BY Field1,Field2)

It looks legitimate but produces an error

ElevateDB Error #700 An error was found in the statement at line 0 and
column 0 (Invalid expression "MIN of Id" found, ORDER BY clause not allowed)

Using the same query with a derived table does seem to work

DELETE FROM Table WHERE Id NOT IN
(SELECT DeleteId FROM (SELECT min(Id) AS DeleteId FROM Table
GROUP BY Field1,Field2) t1)

John

John

>DELETE FROM Table WHERE Id NOT IN
>(SELECT DeleteId FROM (SELECT min(Id) AS DeleteId FROM Table
>GROUP BY Field1,Field2) t1)

That's one of the ways I thought about but (and its a great big BUT) I think that will result in all singleton records being deleted as well and leaving only the first occurrence of duplicate/triplicate etc records.

Roy Lambert

John
>
> >DELETE FROM Table WHERE Id NOT IN
> >(SELECT DeleteId FROM (SELECT min(Id) AS DeleteId FROM Table
> >GROUP BY Field1,Field2) t1)
>
> That's one of the ways I thought about but (and its a great big BUT) I
think that will result in all singleton records being deleted as well and
leaving only the first occurrence of duplicate/triplicate etc records.

I don't think so.  We are selecting for deletion only those records which do
not have the smallest id.

If we had only 1 record in the table it would not be deleted.

John

John


>I don't think so. We are selecting for deletion only those records which do
>not have the smallest id.
>
>If we had only 1 record in the table it would not be deleted.

Very good point John. I think that having just sorted out my deduping stuff my brain wouldn't encompass something that simple

Roy Lambert

John,

<< It looks legitimate but produces an error >>

I'll check this out and make sure that it is fixed.

--
Tim Young
Elevate Software
www.elevatesoft.com