title: Finding the difference between two values in the same column in MySQL
body: <p>I have a simple table called that contains share prices in MySQL:</p>
  
  <pre><code>Table `share_prices`
  
  +----------+-------+---------------------+
  | stock_id | price | date                |    
  +----------+-------+---------------------+    
  |        1 |  0.05 | 2010-02-24 01:00:00 |
  |        2 |  3.25 | 2010-02-24 01:00:00 |
  |        3 |  3.30 | 2010-02-24 01:00:00 |
  |        1 |  0.50 | 2010-02-23 23:00:00 |
  |        2 |  1.90 | 2010-02-23 23:00:00 | 
  |        3 |  2.10 | 2010-02-23 23:00:00 |
  |        1 |  1.00 | 2010-02-23 19:00:00 |
  |        2 |  1.00 | 2010-02-23 19:00:00 | 
  |        3 |  1.00 | 2010-02-23 19:00:00 | 
  +----------+-------+---------------------+
  </code></pre>
  
  <p>Every time a share price is updated, a new row is inserted into the table.</p>
  
  <p>With this structure, <strong>how can I return a query that shows the price change in the last 24 hours?</strong></p>
  
  <p>The desired result would be:</p>
  
  <pre><code>+----------+------+------+------------+
  | stock_id | then | now  | difference |
  +----------+------+------+------------+    
  |        3 | 1.00 | 3.30 |       2.30 |
  |        2 | 1.00 | 3.25 |       2.25 |
  |        1 | 1.00 | 0.05 |      -0.95 |
  +----------+------+------+------------+
  </code></pre>
  
  <p>What's the best way to go about this? Some kind of join? A sub-query?</p>
  
  <p>What I think I'm aiming for is to essentially query once to get <code>then</code>, query again to get <code>now</code> and then somehow glue it all together at the end.</p>
  
  <p>Edit: I need to account for negative changes too.</p>