title: correlated query to get begin sequence and end sequence in sql oracle
body: <p>I have a table like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>employee type beginSeq endSeq
  A        loa  1        3
  A        loa  2        3
  A        loa  4        5
  A        loa  6        8
  A        loa  7        8
  </code></pre>
  
  <p>I thought doing two tables. 
  a table <code>loa</code>=</p>
  
  <pre><code>select * from table where type='loa'
  </code></pre>
  
  <p>and a table <code>rfl</code>=</p>
  
  <pre><code>    select * from table where type='rfl'
  </code></pre>
  
  <p>Then I just thought I could do this:</p>
  
  <pre><code>select loa.* from loa
      left join (select min(seq) from rfl where rfl.seq&gt;=loa.seq)
      on rfl.employee = loa.employee
  </code></pre>
  
  <p>but unfortunately the loa.seq in the subquery is not known at that point.
  Someone a suggestion?</p>