I *mostly* agree with Aaron. A couple of points:
1. For some DB engines, GROUP BY doesn't work in sub-queries (cor-related
queries). In Aaron's SQL, I'd say the GROUP BY is unnecessary anyway due to
the rest of his WHERE clause which, in conjunction with the COUNT(*),
narrows things down to a single record result anyway.
2. Aaron's SQL will delete ALL order records for a particular customer if
that customer has more than 100 orders. I think you want to keep the first
100 or something? I base this assumption on your mention of "some_field",
which I also assume to be a sequential id.
Here's my suggestion, a modification of Aaron's SQL:
DELETE FROM ORDERS A
WHERE A.SOME_FIELD > 100
AND 100<(SELECT COUNT(*)
FROM ORDERS O
WHERE O.CUST=A.CUST)
If you want to keep the LAST 100 orders for the customer:
DELETE FROM ORDERS A
WHERE A.SOME_FIELD > 100
AND 100<(SELECT COUNT(*)
FROM ORDERS O
WHERE O.CUST=A.CUST)
AND A.SOME_FIELD + 100 < (SELECT MAX(O2.SOME_FIELD)
FROM ORDERS
O2
WHERE
O2.CUST=A.CUST)
Cheers.
BJ...
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
