Re: getting multiple Newest records

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Wed, 29 Jan 2003 01:11:50 GMT
Message-ID: <Xns9311CD00CA1EEpobox002bebubcom@204.127.202.16>

jb <jbecker_at_fhcrc.org.righteo> wrote in news:3E371429.8050005 @fhcrc.org.righteo:

> Steve Wicklund wrote:
> 

>>Hello all;
>>
>> Given a table where there are columns id (varchar), description
>>(varchar), and date (date), where none of the columns are unique; I am
>>trying to select the 'newest' row (as determined by date col) of each
>>id. <<snip>>
>>
>>rowId id description date
>>_______________________________
>>row1 01 yada 01-10
>>row2 02 yada2 01-10
>>row3 01 newYada 01-12
>>row4 02 yada3 01-20
>>row5 03 yadayada 02-20
>>row5 04 yada5 02-01
>>
>><<snip>>
>>

> SQL> select myid, descrip, d_event
>   2  from yada
>   3  where (myid, d_event) in
>   4        (select myid, max(d_event) from yada group by myid);
> 
>      MYID DESCRIP                                  D_EVENT
> --------- ---------------------------------------- ----------
>         1 quit                                     01/21/2003
>         2 ordered                                  01/12/2003
>         3 registered                               01/16/2003
> 
> someone has a faster way i'm open to that.  
> 
> cheers
> jrb

If you have 8i (I think) you can use the analytic function row_number and an in line view which is quite fast.

SQL> select * from t;

ROWID ID DESCRIPTION ID_DATE

----- -- ------------ ---------
row1  01 yada         10-JAN-03
row2  02 yada2        10-JAN-03
row3  01 newYada      12-JAN-03
row4  02 yada3        20-JAN-03
row5  03 yadayada     20-FEB-03
row5  04 yada5        01-FEB-03

6 rows selected.

SQL> select * from (

  2     select row_id, id, description, id_date,
  3        row_number() over (partition by id order by id_date desc) x
  4     from t)

  5 where x = 1;

ROWID ID DESCRIPTION ID_DATE X

----- -- ------------ --------- ----------
row3  01 newYada      12-JAN-03          1
row4  02 yada3        20-JAN-03          1
row5  03 yadayada     20-FEB-03          1
row5  04 yada5        01-FEB-03          1

If you want to use them in PL/SQL you need to use native dynamic SQL until 9i though.

Martin Received on Tue Jan 28 2003 - 19:11:50 CST