title: How to do self join on min/max
body: <p>I am new to sql queries.
  Table is defined as</p>
  
  <pre><code>(  symbol varchar,
     high int,
     low int,
     today date,
     Primary key (symbol, today)
  )
  </code></pre>
  
  <p>I need to find for each symbol in a given date range, <code>max(high)</code> and <code>min(low)</code> and corresponding dates for <code>max(high)</code> and <code>min(low)</code>.</p>
  
  <ul>
  <li>Okay to get first max date and min date in given table.</li>
  <li>In a given date range some dates may be missing. If start date is not present then next date should be used and if last date is not present then earlier available date should be used</li>
  </ul>
  
  <p>Data is for one year and around 5000 symbols.</p>
  
  <p>I tried something like this</p>
  
  <pre><code>SELECT a.symbol,
         a.maxValue,
         a.maxdate,
         b.minValue,
         b.mindate
  FROM (
      SELECT table1.symbol, max_a.maxValue, max_a.maxdate
      FROM table1
      INNER JOIN (
          SELECT table1.symbol,                            
                 max(table1.high) AS maxValue, 
                 table1.TODAY AS maxdate
          FROM table1
          GROUP BY table1.symbol
      ) AS max_a  
      ON max_a.symbol = table1.symbol
      AND table1.today = max_a.maxdate
  ) AS a  
  INNER JOIN (
      SELECT symbol,
             min_b.minValue,
             min_b.mindate 
      FROM table1
      INNER JOIN (
          SELECT symbol,
                 min(low) AS minValue, 
                 table1.TODAY AS mindate
          FROM table1
          GROUP BY testnsebav.symbol
      ) AS min_b
      ON min_b.symbol = table1.symbol
      AND table1.today = min_b.mindate
  ) AS b
  ON a.symbol = b.symbol
  </code></pre>