Hi!

Is there a way to compare data from two row (current and next row) of
data in Oracle by just one SQL statement?

I have a table looks like this

f1 f2
------------------- ----
2005 06 01 14:30:00 100
2005 06 01 14:37:21 120

How can I compare the time differences betwen 1st and 2nd rows by just
using one SQL statement.


Appreciate you help. Thanks

Bob

<me@csgoh.com> wrote in message
news:1117699531.304670.88710@g44g2000cwa.googlegro ups.com...
> Hi!
>
> Is there a way to compare data from two row (current and next row) of
> data in Oracle by just one SQL statement?
>
> I have a table looks like this
>
> f1 f2
> ------------------- ----
> 2005 06 01 14:30:00 100
> 2005 06 01 14:37:21 120
>
> How can I compare the time differences betwen 1st and 2nd rows by just
> using one SQL statement.

You can use analytic function LAG

>
>
> Appreciate you help. Thanks
>
> Bob
>

Yea, analytics will do this for you. Here is an example:-

Create a table similar to yours:-

>create table t1 (id integer, dtm date, value integer);

Insert some values:-

>insert into t1 values (1, sysdate-1,100);
>insert into t1 values (1, sysdate-2,200);
>insert into t1 values (1, sysdate-3,300);
>insert into t1 values (2, sysdate-4,400);
>insert into t1 values (2, sysdate-5,500);
>insert into t1 values (2, sysdate-6,600);

So, you want to find each row, with the date in the next row:-

select id,
dtm this_row_time,
value,
lead(dtm) over (order by dtm desc) next_row_time
from t1
order by dtm desc;


ID THIS_ROW_ VALUE NEXT_ROW_
---------- --------- ---------- ---------
1 01-JUN-05 100 31-MAY-05
1 31-MAY-05 200 30-MAY-05
1 30-MAY-05 300 29-MAY-05
2 29-MAY-05 400 28-MAY-05
2 28-MAY-05 500 27-MAY-05
2 27-MAY-05 600

6 rows selected.


Or if you want to group the results up, with those that have the same
ID (maby this is items in a time line associated with an order or
something)

select id,
dtm this_row_time,
value,
lead(dtm) over (partition by id order by dtm desc)
next_row_time
from t1
order by dtm desc;

ID THIS_ROW_ VALUE NEXT_ROW_
---------- --------- ---------- ---------
1 01-JUN-05 100 31-MAY-05
1 31-MAY-05 200 30-MAY-05
1 30-MAY-05 300
2 29-MAY-05 400 28-MAY-05
2 28-MAY-05 500 27-MAY-05
2 27-MAY-05 600

6 rows selected.

To get the difference between the dates simply subtract this_row_date
from next_row_date (i think the result is an integer in days).

Hope this helps ...