RE: selecting last occurrence of each record through query

Guys,

I've tried Michael's method but get a record for every transaction.

I'm doing this over a bespoke file which contains costing details.

All I'm interested in is the following details, product code Year and Week 
No.  So I'm sorting by product, year, and Week and selecting Max on Year & 
Week.
Form of output is set to 2 (summary).   Surely all I should be getting is 
the last record. as shown below.

Item             Sales  Period
                     Year     /week
AF200             103      40
              MAX 103      40
 
AF200             104       1
              MAX 104       1
 
AF200             104       6
              MAX 104       6

tia

Jon
 

Jon_Wadey@xxxxxxxxxxxxxxx
Supercook Website




"Lemon, Michael" <MLemon@xxxxxxxxxxx>
Sent by: system21-bounces@xxxxxxxxxxxx
06/08/2004 16:09
Please respond to System 21 Users

 
        To:     "'System 21 Users'" <system21@xxxxxxxxxxxx>
        cc: 
        Subject:        RE: [SYSTEM21]   selecting last occurrence of each 
record through query


Both of which are very good points.

-----Original Message-----
From: Joe Roesch [mailto:jroesch@xxxxxxxxxxxxxxx] 
Sent: Friday, August 06, 2004 11:08 AM
To: 'System 21 Users'
Subject: RE: [SYSTEM21] 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.

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