CODEWITHSUNDEEP
sqloracle-database
Bigjo
Bigjo

Reputation: 633

correlated query to get begin sequence and end sequence in sql oracle

I have a table like this:

employee type seq
A        loa  1
A        loa  2
A        rfl  3
A        loa  4
A        rfl  5
A        loa  6
A        loa  7
A        rfl  8

now I want a table that gives me for all loa a begin sequence and an end sequence. The begin sequence is just the current sequence of that row. The end sequence is sequence is the first rfl after that sequence. So for my example the result has to be:

employee type beginSeq endSeq
A        loa  1        3
A        loa  2        3
A        loa  4        5
A        loa  6        8
A        loa  7        8

I thought doing two tables. a table loa=

select * from table where type='loa'

and a table rfl=

    select * from table where type='rfl'

Then I just thought I could do this:

select loa.* from loa
    left join (select min(seq) from rfl where rfl.seq>=loa.seq)
    on rfl.employee = loa.employee

but unfortunately the loa.seq in the subquery is not known at that point. Someone a suggestion?

Upvotes: 1

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

In Oracle, I would use lead():

select *
from (select loa.*,
             lead(case when type = 'rfl' then seq end ignore nulls) over (partition by employee order by seq) as end_seq
      from loa
     ) loa
where type = 'loa';

The ignore nulls pulls the next non-null value. The case statement is only looking for 'rfl' values.

You could also express this as:

select *
from (select loa.*,
             min(case when type = 'rfl' then seq end) over (partition by employee order by seq rows between current row and unbounded following) as end_seq
      from loa
     ) loa
 where type = 'loa';

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

You might use correlated subquery as :

select t.*,
       ( select min(seq) from tab where type = 'rfl' and seq > t.seq )
       as endSeq
  from tab t
 where t.type = 'loa'
 order by t.seq;

Rextester Demo

Upvotes: 3

Related Questions