unqiue records

[Betsey Thurlo]

Hi.
I'd like to create a sql statement in a query that does
this:

If record1/field1 is equal to record2/field1 AND
record1/field2 is equal to record2/field2 AND
record1/field3 or record2/field3 is NULL then delete the
record with the field3 NULL value.

Then If record1/field1 is equal to record2/field1 AND
record1/field2 is equal to record2/field2 delete all of
the identical records except for one (doesn't matter
which one since the Null record is already gone).

BDT

 

[solex]

Betsey,

There is no way to create are single query to do what you want. But you
could create some inline views to compare values from different records:

SELECT r1.f3, r2.f3
FROM
(SELECT f1, f2, f3 FROM YourTable WHERE ...) r1,
(SELECT f1, f2, f3 FROM YourTable WHERE ...) r2
WHERE
r1.f1 = r2.f1 AND r1.f2 = r2.f2 AND (r1.f3 IS NULL OR r2.f3 IS NULL)