title: MySQL query comparing values to previous rows' values
body: <p>I've been searching but have been unable to find a solution to this--I know it's do-able but I just don't have the ninja SQL skills I need (yet)....</p>
  
  <p>I'm looking for a solution to this issue: I have a 2 tables related to stock market data.  The first is a simple list of stock symbols with an ID and stock ticker symbol (ID,SYMBOL).  The second table contains historical price data for each of the stocks.  (ID, DATE, OPEN, HIGH, LOW, CLOSE, VOLUME).</p>
  
  <p>I'm trying to figure out how to query for stocks that have the most recent CLOSE price that is greater than their CLOSE price 5 trading-days ago.  I can't just do date math because the stocks don't trade every day (no trading on weekends &amp; holidays, as well as some stocks may not trade on a normal trading day).  Thus, I just need to compare the CLOSE price from most recent row and the 5th row proceeding it for each symbol.</p>
  
  <p>I have sample tables and data here: 
  <a href="http://sqlfiddle.com/#!2/5fe76/2" rel="nofollow">http://sqlfiddle.com/#!2/5fe76/2</a></p>
  
  <pre><code>CREATE TABLE `STOCKS` (
    `ID` int,
    `SYMBOL` varchar(10)
  );
  
  INSERT INTO `STOCKS` (`ID`,`SYMBOL`)
  VALUES
    (1, 'AA'),
    (2, 'ADT'),
    (3, 'AEO'),
    (4, 'AFA');
  
  CREATE TABLE `PRICES` (
      `ID` int,
      `DATE` date,
      `OPEN` decimal(6,2),
      `HIGH` decimal(6,2),
      `LOW` decimal(6,2),
      `CLOSE` decimal(6,2),
      `VOLUME` bigint
    );
  
  INSERT INTO `PRICES` (`ID`,`DATE`,`OPEN`,`HIGH`,`LOW`,`CLOSE`,`VOLUME`) VALUES
  (1, '2014-11-06',   16.37,  16.42,  16.15,  16.37,  14200400),
  (1, '2014-11-05',   16.68,  16.69,  16.17,  16.26,  18198200),
  (1, '2014-11-04',   16.85,  16.87,  16.43,  16.56,  13182800),
  (1, '2014-11-03',   16.78,  17.03,  16.65,  16.93,  15938500),
  (1, '2014-10-31',   16.43,  16.76,  16.24,  16.76,  18618300),
  (1, '2014-10-30',   16.17,  16.36,  15.83,  16.22,  17854400),
  (1, '2014-10-29',   16.58,  16.70,  16.05,  16.27,  31173000),
  (1, '2014-10-28',   16.5,   16.65,  16.41,  16.60,  12305900),
  (1, '2014-10-27',   16.56,  16.57,  16.31,  16.38,  15452900),
  (1, '2014-10-24',   16.33,  16.57,  16.22,  16.55,  12840200),
  
  (2, '2014-11-06',   35.9,   36.12,  35.75,  36.07,  1018100),
  (2, '2014-11-05',   35.68,  35.99,  35.37,  35.96,  1101500),
  (2, '2014-11-04',   35.13,  35.69,  35.02,  35.49,  819100),
  (2, '2014-11-03',   35.81,  35.99,  35.27,  35.32,  1304500),
  (2, '2014-10-31',   35.79,  35.86,  35.46,  35.84,  1319400),
  (2, '2014-10-30',   34.7,   35.34,  34.66,  35.19,  1201800),
  (2, '2014-10-29',   35.06,  35.56,  34.5,   34.92,  1359000),
  (2, '2014-10-28',   34.32,  35.17,  34.15,  35.07,  1301800),
  (2, '2014-10-27',   34.2,   34.2,   33.66,  34.1,   662600),
  (2, '2014-10-24',   34.02,  34.54,  33.95,  34.5,   750600),
  
  (3, '2014-11-06',   13.27,  13.92,  13.25,  13.82,  6518000),
  (3, '2014-11-05',   12.95,  13.27,  12.74,  13.22,  8716700),
  (3, '2014-11-04',   12.85,  12.94,  12.65,  12.89,  4541200),
  (3, '2014-11-03',   12.91,  13.12,  12.73,  12.89,  4299100),
  (3, '2014-10-31',   13.2,   13.23,  12.83,  12.87,  7274700),
  (3, '2014-10-30',   12.83,  12.91,  12.68,  12.86,  4444300),
  (3, '2014-10-29',   13.02,  13.20,  12.79,  12.91,  2974900),
  (3, '2014-10-28',   12.87,  13.10,  12.52,  13.04,  7365600),
  (3, '2014-10-27',   12.84,  13.00,  12.67,  12.92,  6647900),
  (3, '2014-10-24',   13.26,  13.29,  12.60,  12.92,  12803300),
  
  (4, '2014-11-06',   24.59,  24.59,  24.49,  24.55,  20400),
  (4, '2014-11-05',   24.81,  24.9,   24.81,  24.88,  11800),
  (4, '2014-11-04',   24.87,  24.88,  24.76,  24.88,  10600),
  (4, '2014-11-03',   24.85,  24.88,  24.76,  24.81,  18100),
  (4, '2014-10-31',   24.82,  24.85,  24.77,  24.78,  8100),
  (4, '2014-10-30',   24.83,  24.87,  24.74,  24.79,  13900),
  (4, '2014-10-29',   24.86,  24.86,  24.78,  24.81,  5500),
  (4, '2014-10-28',   24.85,  24.85,  24.80,  24.84,  10600),
  (4, '2014-10-27',   24.68,  24.85,  24.68,  24.85,  7700),
  (4, '2014-10-24',   24.67,  24.82,  24.59,  24.82,  9300);
  </code></pre>
  
  <p>Pseudo code for the query would be something like this:
  "Find symbols whos most recent closing prices is greater than the closing price 5 trading-days earlier"</p>
  
  <p>The query I'd like to create should result in the following:</p>
  
  <pre><code>Date        Symbol   Close   Close(-5)
  2014-11-06  AA       16.37   16.22
  2014-11-06  ADT      36.07   35.19
  2014-11-06  AEO      13.82   12.86
  </code></pre>
  
  <p>(the symbol 'AFA' would not match as it's recent close is 24.55 and 5 rows prior it was 24.75)</p>