title: Oracle - grouping between pairs of records
body: <p>I have a logging table that contains data that looks like this:</p>
  
  <pre>
  ID     MSG                DATE
  ---------------------------------------------
  1      TEst               2010-01-01 09:00:00
  2      Job Start          2010-01-01 09:03:00
  3      Do something       2010-01-01 09:03:10
  4      Do something else  2010-01-01 09:03:12
  5      Do something       2010-01-01 09:04:19
  6      Job End            2010-01-01 09:06:30
  7      Job Start          2010-01-01 09:18:03
  8      Do something       2010-01-01 09:18:17
  9      Do other thing     2010-01-01 09:19:48
  10     Job End            2010-01-01 09:20:27
  </pre>
  
  <p>It contains (among other things) messags written by the application. I am interested in producing a report of everything that gets written between a "Job Start" record and a "Job End" record, for all such pairs of "Job Start" and "Job End". Ideally the report would look like this:</p>
  
  <pre>
  BATCH_NUM   ID     MSG                DATE
  ---------------------------------------------------------         
  1           3      Do something       2010-01-01 09:03:10
  1           4      Do something else  2010-01-01 09:03:12
  1           5      Do something       2010-01-01 09:04:19
  
  2           8      Do something       2010-01-01 09:18:17
  2           9      Do other thing     2010-01-01 09:19:48
  </pre>
  
  <p>(line break between batches added to make it easier to read)</p>
  
  <p>The output report omits the "Job STart and "Job End" messages, as well as the "TEst" message (which exists outside a pair of "Job Start" and "Job End".</p>
  
  <p>I'm not even sure where to begin writing this kind of query, or if it's better to go with PL/SQL for this. Heck, I'm not even sure if there's a proper technical term for what I'm trying to do. ;)</p>
  
  <p>(oracle version is 10g)</p>