title: SQL Query to Collapse Duplicate Values By Date Range
body: <p>I have a table with the following structure: ID, Month, Year, Value with values for one entry per id per month, most months have the same value.</p>
  
  <p>I would like to create a view for that table that collapses the same values like this: ID, Start Month, End Month, Start Year, End Year, Value, with one row per ID per value.</p>
  
  <p>The catch is that if a value changes and then goes back to the original, it should have two rows in the table</p>
  
  <p>So:</p>
  
  <ul>
  <li>100 1 2008 80</li>
  <li>100 2 2008 80</li>
  <li>100 3 2008 90</li>
  <li>100 4 2008 80</li>
  </ul>
  
  <p>should produce</p>
  
  <ul>
  <li>100 1 2008 2 2008 80</li>
  <li>100 3 2008 3 2008 90</li>
  <li>100 4 2008 4 2008 80</li>
  </ul>
  
  <p>The following query works for everything besides this special case, when the value returns to the original.</p>
  
  <pre><code>select distinct id, min(month) keep (dense_rank first order by month) 
  over (partition   by id, value) startMonth, 
  max(month) keep (dense_rank first order by month desc) over (partition
  by id, value) endMonth, 
  value
  </code></pre>
  
  <p>Database is Oracle</p>