By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

Urgent Query

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.