ORACLE SQL data from next recordset in current recordset

Question

SELECT
  tsn_ref,
  current_node
FROM rtdev.trip_bodies cnode
WHERE thr_id = 1
ORDER BY arrival_time

The above query gives me 9 records each with 2 columns, quite straigh forward I want to add a 3rd column, which will contain the 'TSN_REF' field from the subsequent (time sorted) recordset.

So output would be along the lines of:

+---------+--------------+------+
| TSN_REF | CURRENT_NODE | TSN2 |
+---------+--------------+------+
|       1 |            1 | 2    |
|       2 |            2 | 4    |
|       4 |           10 | 8    |
|       8 |           13 | 13   |
|      13 |           16 | NULL | (no subsequent entry)
+---------+--------------+------+

Any suggestions on how I could modify the query to achieve this?

Answer 1

You can do this using the LEAD function: http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php

it would go something like this (this is untested....)

 SELECT TSN_REF, 
        CURRENT_NODE,
        LEAD(TSN_REF, 1, '') OVER (ORDER BY ARRIVAL_TIME) TSN2
   FROM RTDEV.TRIP_BODIES CNODE 
  WHERE (THR_ID = 1) 
  ORDER BY ARRIVAL_TIME

edited: fixed order by in lead

Update

See if this works....

select ts_id,
       tsn_ref,
       current_node,
       case when ts_id = ts_id_next then tsn_ref_next else null end tsn_ref_next 
 from (
   with q as (
      select 1 ts_id, 1 tsn_ref,   1 current_node, sysdate - 20 arrival_time from dual
      union
      select 1 ts_id, 2 tsn_ref,   2 current_node, sysdate - 19 arrival_time from dual
      union
      select 1 ts_id, 4 tsn_ref,  10 current_node, sysdate - 18 arrival_time from dual
      union
      select 1 ts_id, 8 tsn_ref,  13 current_node, sysdate - 17 arrival_time from dual
      union
      select 1 ts_id, 13 tsn_ref, 16 current_node, sysdate - 16 arrival_time from dual
      union
      select 2 ts_id, 1 tsn_ref,   1 current_node, sysdate - 20 arrival_time from dual
      union
      select 2 ts_id, 2 tsn_ref,   2 current_node, sysdate - 19 arrival_time from dual
      union
      select 2 ts_id, 4 tsn_ref,  10 current_node, sysdate - 18 arrival_time from dual
      union
      select 2 ts_id, 8 tsn_ref,  13 current_node, sysdate - 17 arrival_time from dual
      union
      select 2 ts_id, 13 tsn_ref, 16 current_node, sysdate - 16 arrival_time from dual
     )
select ts_id, 
   tsn_ref, 
   current_node,
   lead(tsn_ref, 1, '') over (order by ts_id, arrival_time) tsn_ref_next,
   lag(ts_id, 1, ts_id) over (order by ts_id, arrival_time)    ts_id_next
from q
)

You will want to use your table, so something like this:

select ts_id,
       tsn_ref,
       current_node,
       case when ts_id = ts_id_next then tsn_ref_next else null end tsn_ref_next 
 from (
       select ts_id, 
              tsn_ref, 
              current_node,
              arrival_time,
              lead(tsn_ref, 1, '') over (order by ts_id, arrival_time) tsn_ref_next,
              lag(ts_id, 1, ts_id) over (order by ts_id, arrival_time)    ts_id_next
          FROM RTDEV.TRIP_BODIES CNODE 
         ORDER BY ARRIVAL_TIME  -- this order by may not be needed
     )
  ORDER BY ARRIVAL_TIME