Search code examples
mysqlsqlgroup-bycorrelated-subquery

SQL: selecting the second-lowest value (via subquery) with GROUP BY clause

Given a database of decimal values, grouped into locations ...

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)
;

It's easy to select the lowest value for a location:

SELECT LocationID,min(LowPrice) FROM ItemPrices GROUP BY LocationID;

LOCATIONID  MIN(LOWPRICE)
10001       100
10002       10

But using a subquery to filter out the lowest value and return the next lowest value doesn't work as expected:

SELECT LocationID,min(LowPrice) FROM ItemPrices
WHERE LowPrice >
(SELECT min(LowPrice)
    FROM ItemPrices)
GROUP BY LocationID;

LOCATIONID  MIN(LOWPRICE)
10001       100
10002       30

The result should show LocationID 10001's second-lowest price as 200, not 100.

Seems the subquery is just returning the lowest LowPrice (10) in all cases. The proper logic would be to find the lowest price for that particular LocationID (100), then find a larger value for that LocationID (200) and return that larger value.

How can I make the subquery aware that it should be evaluating only a subset of the prices?

Just putting a GROUP BY in there (LowPrice > (SELECT min(LowPrice) FROM ItemPrices GROUP BY LocationID)) tells me Subquery returns more than 1 row -- right, of course, it returns one row for every LocationID.

Solution

  • In typing in this question, I have hit on the idea that I need a correlated subquery. The first step is to alias the table ItemPrices. I'm using i for the outer query and s for the subquery.

    SELECT i.LocationID,min(LowPrice) FROM ItemPrices i
WHERE
LowPrice >
(SELECT min(LowPrice)
 FROM ItemPrices s
WHERE i.LocationID = s.LocationID)
GROUP BY LocationID;

    Let's run it!

    LOCATIONID  MIN(LOWPRICE)
10001       200
10002       30

    Looking good! What you think?

    SQLFiddle: http://sqlfiddle.com/#!2/341ce/5