Re: Urgent Query

Hello,

"Windsurfer" <nsingh49_at_yahoo.com> wrote in message news:14bd0e63.0401131041.5d0a521d_at_posting.google.com...
> Here is my question:
>
> create table x (t varchar2(10), dt date);
>
> data in the table x( Keep in mind, I actual table it has over 3
> million rows, so performance is critical )
>
> t dt
> ------------- ------------
> 1001 01/01/2004 10:10:00
> 1001 01/01/2004 10:11:00
> 1001 01/01/2004 10:12:00
> 1001 01/01/2004 10:13:00
> 1002 01/01/2004 11:10:00
> 1002 01/01/2004 11:11:00
> 1002 01/01/2004 11:12:00
> 1002 01/01/2004 11:13:00
>
> I need to write a query to tell me for each row when was the last
> dt/time:
> so for 1001 and 1002 i will have one less record:
> t dt previous_dt
> ------------ ----------------- --------------------
> 1001 01/01/2004 10:11:00 01/01/2004 10:10:00
> 1001 01/01/2004 10:12:00 01/01/2004 10:11:00
> 1001 01/01/2004 10:13:00 01/01/2004 10:12:00
> 1002 01/01/2004 10:11:00 01/01/2004 10:10:00
> 1002 01/01/2004 10:12:00 01/01/2004 10:11:00
> 1002 01/01/2004 10:13:00 01/01/2004 10:12:00
>
> Thanks alot

You cannot do any faster than that -- only one FTS:

select * from
  (select t, dt, lag(dt) over (partition by t order by dt) dt_previous from x)
where dt_previous is not null

Rgds.

VC Received on Tue Jan 13 2004 - 21:07:46 CET