Urgent Query

[Windsurfer]

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

[VC]

Hello,
"Windsurfer" <ns******@yahoo.com> wrote in message
news:14**************************@posting.google.c om...

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