RE: selecting last occurrence of each record through query

You will need to include time as well, perhaps by concatenating both Date
and time prior to your aggregate calculation.  Don't forget to use the
system date and not the user date.     


-----Original Message-----
From: Lemon, Michael [mailto:MLemon@xxxxxxxxxxx] 
Sent: Friday, August 06, 2004 10:41 AM
To: 'System 21 Users'
Subject: RE: [SYSTEM21] 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.

_______________________________________________
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.