RE: selecting last occurrence of each record through query

This can be accomplished by running 2 QUERY/400 queries...

1) Sort by the unique keys, place a break of 1 on all of those key fields,
use a summary function to select the MAX for the field that defines "latest"
and out put the summary data to a file.

2) join this file back to the transactional file using all the unique keys
and the MAX field.

I'm not sure if there is a way to retrieve the relative record number in
QUERY/400, so if that is what you are trying to use to determine latest, I
can't help you there.



Something similar can be accomplished with a single SQL statement

If you are using date to determine latest...

Select a.* 
from myfile a 
   inner join
     (select keyfield, max(datefield) as datefield from myfile group by
keyfield) b
   on a.keyfield=b.keyfield and a.datefield=b.datefield

If you are trying to use relative record number to determine latest...

Select a.* 
from myfile a 
   inner join
     (select keyfield, max(rrn(b)) as relrecnum from myfile group by
keyfield) b
   on a.keyfield=b.keyfield
Where rrn(a)=b.relrecnum


Hope this helps in some way.  I didn't syntax check this stuff, so if you
have problems using it, send me an email and I'll try to debug it.


-Mike

-----Original Message-----
From: Jonathan Wadey [mailto:Jon_Wadey@xxxxxxxxxxxxxxxxxx] 
Sent: Friday, August 06, 2004 10:31 AM
To: system21@xxxxxxxxxxxx
Subject: [SYSTEM21] selecting last occurrence of each record through query


_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.