title: MySQL query to get daily differential values
body: <p>I want to make a MySQL to get daily differential values from a table who looks like this:</p>
  
  <pre><code>Date               | VALUE
  --------------------------------
  "2011-01-14 19:30" |   5
  "2011-01-15 13:30" |   6
  "2011-01-15 23:50" |   9
  "2011-01-16 9:30"  |   10
  "2011-01-16 18:30" |   15
  </code></pre>
  
  <p>I have made two subqueries. The first one is to get the last daily value, because I want to compute the difference values from this data:</p>
  
  <pre><code>SELECT r.Date, r.VALUE
      FROM table AS r
      JOIN (
      SELECT DISTINCT max(t.Date) AS Date
      FROM table AS t
      WHERE t.Date &lt; CURDATE() 
      GROUP BY DATE(t.Date) 
      ) AS x USING (Date)
  </code></pre>
  
  <p>The second one is made to get the differential values from the result of the first one (I show it with "table" name):</p>
  
  <pre><code>SELECT Date, VALUE - IFNULL( 
      (SELECT MAX( VALUE ) 
      FROM table
      WHERE Date &lt; t1.table) , 0) AS diff
      FROM table AS t1
      ORDER BY Date
  </code></pre>
  
  <p>At first, I tried to save the result of first query in a temporary table but <a href="http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html" rel="nofollow">it's not possible to use temporary tables with the second query</a>. If I use the first query inside the FROM of second one between () with an alias, the server complaints about table alias doesn't exist. How can get a something like this:</p>
  
  <pre><code> Date               | VALUE
   ---------------------------
   "2011-01-15 00:00" |   4
   "2011-01-16 00:00" |   6
  </code></pre>