Re: select distinct, max, fetch 1 row, something else?

[Raul A Jager <raul@xxxxxxxxxx>]

This will do it, but it may not be very fast..

select master, event, timestamp from master join detail on 
master_key=detail_key
    where timentamp in (select timestamp from
           ( select max(timestamp), master_key from detail group by 
master_key))

There may be some syntax errors, you probably need to reaname some of 
the tables using "as".
__________________________________________________________________________________    

ile rpg wrote:

> I have a master file supported by a transactions file; the 
> transactions file includes an event code (possibly duplicated) and 
> timestamp.
>
> I'm trying to develop an embedded SQL statement to join the master and 
> transaction files and to return a record set consisting of one row per 
> master record with the master record key, the /last/ event (based on 
> the timestamp), and the /last/ timestamp.
>
> The timestamp is not in the master file, of course.
>
> I have something like this (which provides duplicate records):
>
> select master, event, timestamp from master
> left outer join detail on master_key=detail_key
> order by master_key,timestamp desc
>
> Here are my problems: DISTINCT appears to work on the whole row and 
> not on a selected columns; and I guess FETCH 1 ROW doesn't work in 
> interactive SQL (or, more likely, /I/ don't know how to use specify 
> "fetch 1 row per order number").
>
> I can solve the problem by eliminating the JOIN and doing a READE 
> against a logical over the transactions file with a descending key on 
> the timestamp; this provides pretty good performance for this 
> interactive application.  Or, if I can figure out how to get one 
> record per master/detail record set, I can figure out how to get the 
> last detail.
>
> My SQL thinking is still developing, and I just can't figure out how 
> to tackle this one.  I'd appreciate any suggestions!
>
> Thanks,
> IRG
>
> _________________________________________________________________
> What are the 5 hot job markets for 2004? Click here to find out. 
> http://msn.careerbuilder.com/Custom/MSN/CareerAdvice/WPI_WhereWillWeFindJobsIn2004.htm?siteid=CBMSN3006&sc_extcmp=JS_wi08_dec03_hotmail1 
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing 
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.