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

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

----- Original Message ----- 
From: "ile rpg" <ile_rpg_guy@xxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Sunday, February 01, 2004 15:09
Subject: select distinct, max, fetch 1 row, something else?


> select master, event, timestamp from master
> left outer join detail on master_key=detail_key
> order by master_key,timestamp desc
> 

close, use a common table expression.....
example....

create table m (k char(1))                                 
                      
create table t (ts timestamp, ev char(1), k char(1))       
                               
with theLast as (select max(ts) as ts, k from t group by k)
  select m.k, t.ts, t.ev                                   
    from t join theLast on t.k = theLast.k                 
    join m on m.k = t.k                                    



===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If you pick up a starving dog and make him prosperous,
  he will not bite you. This is the principal difference
  between a dog and a man."

    - Mark Twain