Oracle LEAD - return next matching column value

Question

I having below data in one table.

And I want to get NEXT out data from OUT column. So used LEAD function in below query.

SELECT ROW_NUMBER,TIMESTAMP,IN,OUT,LEAD(OUT) OVER (PARTITION BY NULL ORDER BY TIMESTAMP) AS NEXT_OUT 
FROM MYTABLE;

It gives data as below NEXT_OUT column.

But I need to know the matching next column value in sequential way like DESIRED columns. Please let me know how can i achieve this in Oracle LEAD FUNCTION

THANKS

IGNORE NULLS may work for row_number 3 but does not work for row_number 4. — usersam, Feb 21 '19 at 12:27

score 1 · Answer 1 · answered Feb 21 '19 at 11:52

1

Enumerate in the "in"s and the "out"s and use that information for matching.

select tin.*, tout.out as next_out
from (select t.*,
             count(in) over (order by timestamp) as seqnum_in
      from t
     ) tin left join
      (select t.*,
             count(out) over (order by timestamp) as seqnum_out
      from t
     ) tout
     on tin.in is not null and
        tout.out is not null and
        tin.seqnum_in = tout.seqnum_out;

answered Feb 21 '19 at 11:52

Gordon Linoff

1,242,037
58
646
786

Thanks @Gordon. This will work but the data shown in ques is dummy data from a table which is going to be very big in size. we can not afford accessing table twice with left join. – usersam Feb 21 '19 at 11:56
@usersam . . . I would suggest you try this before rejecting the answer out-of-hand. – Gordon Linoff Feb 21 '19 at 13:31

Salman A · Accepted Answer · 2019-02-21T18:03:03.000

1

Assign row number to all INs and OUTs separately, sort the results by placing them in a single column and calculate LEADs:

WITH cte AS (
    SELECT t.*
         , CASE WHEN "IN"  IS NOT NULL THEN COUNT("IN")  OVER (ORDER BY "TIMESTAMP") END AS rn1
         , CASE WHEN "OUT" IS NOT NULL THEN COUNT("OUT") OVER (ORDER BY "TIMESTAMP") END AS rn2
    FROM t
)
SELECT cte.*
     , LEAD("OUT") OVER (ORDER BY COALESCE(rn1, rn2), rn1 NULLS LAST) AS NEXT_OUT
FROM cte
ORDER BY COALESCE(rn1, rn2), rn1 NULLS LAST

Demo on db<>fiddle

edited Feb 21 '19 at 18:03

answered Feb 21 '19 at 13:15

Salman A

262,204
82
430
521

Thanks @Salman. This works with (ORDER BY COALESCE(rn1, rn2), rn1). – usersam Feb 21 '19 at 16:47
how to take care if one OUT record is inserted without IN like, INSERT INTO t ("ROW_NUMBER", "TIMESTAMP", "IN", "OUT") VALUES (0, 100, NULL, '100'). I also have some broken data in table. – usersam Feb 21 '19 at 16:49
"in" row should be placed before "out" so `, r1 NULLS LAST` is correct. re: broken data... I have no idea, but it probably needs a different logic; lead/lag might not be able to handle it. – Salman A Feb 21 '19 at 17:59

Oracle LEAD - return next matching column value

2 Answers2