title: Group within group in Oracle SQL
body: <p>I have data in following form:</p>
  
  <pre><code>ID GROUP DATE
  1  A     01-Sep-2017
  1  A     02-Sep-2017
  1  C     03-Sep-2017
  1  C     04-Sep-2017
  1  C     05-Sep-2017
  1  B     06-Sep-2017
  1  B     07-Sep-2017
  1  A     08-Sep-2017
  1  A     09-Sep-2017
  1  C     10-Sep-2017
  </code></pre>
  
  <p>The desired output is as under:</p>
  
  <pre><code>ID GROUP START_DATE  END_DATE
  1  A     01-Sep-2017 02-Sep-2017
  1  C     03-Sep-2017 05-Sep-2017
  1  B     06-Sep-2017 07-Sep-2017
  1  A     08-Sep-2017 09-Sep-2017
  1  C     10-Sep-2017 10-Sep-2017
  </code></pre>
  
  <p>If I try to use simple group by it will club the GROUP field such that A will start from 01-Sep-2017 and end on 09-Sep-2017 whereas it is ending on 02-Sep-2017 and then restarting from 08-Sep-2017. I have tried various analytical functions to first assign a unique number to each of the sub groups to get something like below: </p>
  
  <pre><code>ID GROUP DATE        RANK
  1  A     01-Sep-2017 1
  1  A     02-Sep-2017 1
  1  C     03-Sep-2017 2
  1  C     04-Sep-2017 2
  1  C     05-Sep-2017 2
  1  B     06-Sep-2017 3
  1  B     07-Sep-2017 3
  1  A     08-Sep-2017 4
  1  A     09-Sep-2017 4
  1  C     10-Sep-2017 5
  </code></pre>
  
  <p>Now it is just a case of simple group by on the new field, but cant get the desired RANK column.</p>
  
  <p>Any help on this or any other approach to achieve the same.</p>
  
  <p>Thanks.</p>