title: SQL: selecting the second-lowest value (via subquery) with GROUP BY clause
body: <p>Given a database of decimal values, grouped into locations ...</p>
  
  <pre><code>CREATE TABLE ItemPrices
      (`Company` varchar(11), `LocationID` char(5), `ProductLevel` varchar(11), `LowPrice` decimal(5,2))
  ;
  
  INSERT INTO ItemPrices
      (`Company`, `LocationID`, `LowPrice`)
  VALUES
      ('Company 1', 10001, 100.00),
      ('Company 2', 10001, 200.00),
      ('Company 3', 10001, 300.00),
      ('Company 4', 10001, 400.00),
      ('Company 1', 10002, 10.00),
      ('Company 2', 10002, NULL),
      ('Company 3', 10002, 30.00),
      ('Company 4', 10002, 40.00)
  ;
  </code></pre>
  
  <p>It's easy to select the lowest value for a location:</p>
  
  <pre><code>SELECT LocationID,min(LowPrice) FROM ItemPrices GROUP BY LocationID;
  
  LOCATIONID  MIN(LOWPRICE)
  10001       100
  10002       10
  </code></pre>
  
  <p>But using a subquery to filter out the lowest value and return the next lowest value doesn't work as expected:</p>
  
  <pre><code>SELECT LocationID,min(LowPrice) FROM ItemPrices
  WHERE LowPrice &gt;
  (SELECT min(LowPrice)
      FROM ItemPrices)
  GROUP BY LocationID;
  
  LOCATIONID  MIN(LOWPRICE)
  10001       100
  10002       30
  </code></pre>
  
  <p>The result should show LocationID 10001's second-lowest price as 200, not 100.</p>
  
  <p>Seems the subquery is just returning the lowest <code>LowPrice</code> (10) in all cases. The proper logic would be to find the lowest price for that particular <code>LocationID</code> (100), then find a larger value for that <code>LocationID</code> (200) and return that larger value.</p>
  
  <p>How can I make the subquery aware that it should be evaluating only a subset of the prices?</p>
  
  <p>Just putting a <code>GROUP BY</code> in there (<code>LowPrice &gt; (SELECT min(LowPrice) FROM ItemPrices GROUP BY LocationID)</code>) tells me <code>Subquery returns more than 1 row</code> -- right, of course, it returns one row for every <code>LocationID</code>.</p>