title: how to calculate days difference of vaules in same column?
body: <p>I have data like this:</p>
  
  <pre><code>select * from date_table;
   startdate
  2018-08-22
  2018-08-24
  2018-08-27
  2018-08-29
  2018-08-31
  2018-09-05
  2018-09-07
  2018-09-10
  </code></pre>
  
  <p>I have written this query, which only gives days difference</p>
  
  <pre><code>CREATE temporary TABLE if not exists results AS (
  select t.startdate, datediff( 
  (select min(t1.startdate) 
  from 
  date_table t1 where
   t1.startdate&gt;t.startdate),
  t.startdate ) days_diff 
  from 
  date_table t) ;
  
  select * from results:
  </code></pre>
  
  <p>Above query gives result as:</p>
  
  <pre><code>startdate   days_diff 
  2018-08-22    2
  2018-08-24    3
  2018-08-27    2
  2018-08-29    2
  2018-08-31    5
  2018-09-05    2
  2018-09-07    3  
  2018-09-10  
  </code></pre>
  
  <p>But I want result like:</p>
  
  <pre><code>startdate     enddate      days_diff 
  2018-08-22   2018-08-24      2
  2018-08-27   2018-08-29      2
  2018-08-31   2018-09-05      5
  2018-09-07   2018-09-10      3
  </code></pre>
  
  <p>I'm using MySQL(version 5.6). Kindly let me know if there is any solution for this problem.
  Thanks in advance.</p>