title: How to fetch consecutive pairs of records in Oracle
body: <p>Can somebody give me a clue about a tricky SQL problem. I searched similar problems, the closest approach seemed this, but it is not the solution to my problem:
  <a href="https://stackoverflow.com/questions/2374084/oracle-grouping-between-pairs-of-records">Oracle - grouping between pairs of records</a></p>
  
  <p>I know this can be achieved by a PL/SQL procedure, but in this case (Oracle) SQL is necessary.
  I am looking for an Oracle SQL SELECT to isolate special records with IDs = 4 and 5 of a plain table. The table consists of just 2 columns: ID and DATE
  It contains data like this:  </p>
  
  <pre><code>ID  DATE          REMARK (this row is not part of table!)
  ---------------   ------------------------------------------
  2   01-JAN-2013     
  4   02-JAN-2013   A  
  7   03-JAN-2013  
  5   05-JAN-2013   A  
  6   07-JAN-2013  
  4   08-JAN-2013   B  
  1   11-JAN-2013  
  5   12-JAN-2013   B  
  
  ... more follows  
  </code></pre>
  
  <p>Groups of IDs 4 and 5 belong together if they follow each in time. Thus rows that I marked "A" belong together, the same is true with "B".
  The two As and two Bs belong together, as they are consecutive in their date.
  Now what I would like to get is a SELECT which returns 4 columns, i. e. the two rows A in one row, as well as rows B in one row.</p>
  
  <p>So the output should look like this:</p>
  
  <pre><code>ID4 DATE4        ID5 DATE5           Comment (no column, just comment)  
  ---------------------------------    ------------------------------------
  4   02-JAN-2013  5   05-JAN-2013     First set of 4 and 5  
  4   08-JAN-2013  5   12-JAN-2013     Second set of 4 and 5  
  
  ...more follows  
  </code></pre>
  
  <p>(Columns ID4 and ID5 are obsolete of course, just for demonstration purpose)</p>
  
  <p>I hope I made myself understandable? Does anybody have an idea?</p>
  
  <hr>
  
  <p>UPDATE:
  Thank you for your ideas and SELECTS, I am sorry for being not clear enough in putting my question.
  Only pairs of 4s and 5s should be considered, and only in ascending order of date. For the example above Alex Pool's and Florin Ghita's solutions are great, THANK YOU!!<br>
  The date of a 4er must be less than or equal to the date of the 5er. If there is a 4er or 5er which has no matching partner, it can be ignored.</p>
  
  <p>But here is another sample with real data and a pitfall (the solutions fail here):  Walking through data in the order of time, there are 2 rows with 5 following consecutively.</p>
  
  <pre><code>4    16.03.2012 17:49:28  A
  5    10.05.2012 09:38:56  A1     Either A1 is possible
  5    12.06.2012 07:51:03  A2     or A2 whichever is easier to code
  4    12.06.2012 08:47:52  B
  5    02.08.2012 11:27:43  B
  4    03.08.2012 13:24:54  C
  5    03.08.2012 14:14:07  C
  4    04.08.2012 15:00:00      should be ignored, as there is no following 5er
  </code></pre>
  
  <p>Wanted output:</p>
  
  <pre><code>4    16.03.2012 17:49:28    5    10.05.2012 09:38:56   (alternat.:  5  12.06.2012 07:51:03)
  4    12.06.2012 08:47:52    5    02.08.2012 11:27:43  
  4    03.08.2012 13:24:54    5    03.08.2012 14:14:07  
  </code></pre>
  
  <p>The suggested SELECTS fail as they do not regard that a 4er must be prior to a 5er in time. If there is no corresponding partner, ignore this row. I had not made myself clear enough, sorry.</p>
  
  <p>Thank you very much
  Friedhelm</p>