This may be a bit after the fact but here goes anyway.
Seems to me the Master (Customer) is a red herring - you're interested in
cleaning up the details (Orders).
If you don't already have one, I suggest an index on listoffiles using
CADid, filedate called byCADiddate (or somesuch).
Query1
select count(CADid) cad, CADid from listoffiles
group by CADid
order by cad desc
tblListoffiles.IndexName := 'byCADidDate';
with Query1 do begin
Open;
while not eof and (Fields[0].asinteger > 149) do begin
with tblListoffiles do begin
SetRange([Query1.Fields[1].asinteger],[Query1.Fields[1].asinteger]);
for i := 1 to Query1.Fields[0].asinteger - 100 do begin
( processing to delete the file )
Delete;
end;
end;
Next;
end;
end;
This will leave you with 100 records (files) for each CAD that started with
150 or more.
Suspect it would also be a tad faster than running master/detail links and
multiple queries.
Regards,
Mike
-----Original Message-----
From: Grant Black <[EMAIL PROTECTED]>
To: Multiple recipients of list database <[EMAIL PROTECTED]>
Date: Thursday, August 12, 1999 5:11 PM
Subject: RE: [DUG-DB]: Common SQL Query
>In the classic customer...orders..stuff/widgets model that I was
>referring to then yes, there would be a third table with widgets per
>order.
>
>In actual fact there is only the 'CADs' table (a list Card Accepting
>Devices which are unique) and a table which is a list of files collected
>from each CAD (equiv to Orders ). To save on disk space I check and if
>there is more than 150 files collected for any CAD then I delete the 50
>oldest.
>
>As the app is still in D1 which does not support nested quries, (uggh -
>but D1 sure seems small & fast after D4), I had to resort to doing a
>loop through the master detail table & triggering a (prepared) query to
>count & delete the oldest files.
>
>I have learnt from BJ's example though, & have flagged the code so in
>the port to D4 it will be replaced by the single Delete query.
>
>Grant Black
>Software Developer
>SmartMove (NZ) Ltd
>Phone: +64 9 361-0219 extn 719
>Fax : +64 9 361-0211
>Email: [EMAIL PROTECTED]
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
