471,073 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Urgent Query

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
Jul 19 '05 #1
1 1631
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by gopim | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.