> 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.
In fact nested queries simply don't work in Delphi 1/16bit BDE/Pdox
anyway but we are ported the app to D4 & the query (with Group By) seems
to work with 32bit BDE & Delphi.
> 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.
Yes - I wanted to keep the last 100 & prune off old 'orders'
> Here's my suggestion, a modification of Aaron's SQL:
> If you want to keep the LAST 100 orders for the customer:
Looks good.. Thanks!
> 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.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
