Hi Lee!

I think your statement should work, but I would still simplify a bit
if I were you:

DELETE FROM Payments P
WHERE
EXISTS
(SELECT * FROM Payments S
WHERE S.PaymentType = P.PaymentType AND
S.PaymentMethod = P.PaymentMethod AND
S.PaymentDate = P.PaymentDate AND
S.PaymentAmount = P.PaymentAmount AND
S.PaymentID < P.PaymentID AND
S.OrderID = P.OrderID
)

I think this statement should consume less resources than your
subselect with group by and having, and if anyone asked me which
duplicate payment was deleted, then I'd be more comfortable answering
'all but the first' ('<') or 'all but the last' ('>'), and not just 'I
have no idea, I think it is random'('<>'). Moreover, I think the SQL
is simpler to understand (though that may also be due to my way of
coding).

HTH,
Set

--- In firebird-support@yahoogroups.com, "Lee Jenkins" wrote:
> I am trying to write a SQL statement to both find and delete
> duplicate records in a DB Table. I've tested this a bit and it seems
> to work.
>
> Can anyone see anything immediately incorrect?
>
> DELETE FROM Payments P
> WHERE
> OrderID IN
> (SELECT S.OrderID FROM Payments S
> GROUP BY S.PaymentID, S.OrderID, S.PaymentType, S.PaymentMethod,
> S.PaymentAmount, S.PaymentDate
> HAVING Count(OrderID) > 0 AND
> S.PaymentType = P.PaymentType AND
> S.PaymentMethod = P.PaymentMethod AND
> S.PaymentDate = P.PaymentDate AND
> S.PaymentAmount = P.PaymentAmount AND
> S.PaymentID <> P.PaymentID AND
> S.OrderID = P.OrderID
> )