[SOLVED] select least row per group in SQL

select least row per group in SQL

I am trying to select the min price of each condition category. I did some search and wrote the code below. However, it shows null for the selected fields. Any solution?

SELECT Sales.Sale_ID, Sales.Sale_Price, Sales.Condition
FROM Items
LEFT JOIN Sales ON ( Items.Item_ID = Sales.Item_ID
AND Sales.Expires_DateTime > NOW( )
AND Sales.Sale_Price = (
SELECT MIN( s2.Sale_Price )
FROM Sales s2
WHERE Sales.`Condition` = s2.`Condition` ) )
WHERE Items.ISBN =9780077225957

Solution

A little more complicated solution, but one that includes your Sale_ID is below.

SELECT TOP 1 Sale_Price, Sale_ID, Condition
FROM Sales
WHERE Sale_Price IN (SELECT MIN(Sale_Price) 
                     FROM Sales 
                     WHERE 
                     Expires_DateTime > NOW() 
                     AND 
                       Item_ID IN     
                         (SELECT Item_ID FROM Items WHERE ISBN = 9780077225957) 
                     GROUP BY Condition )

The 'TOP 1' is there in case more than 1 sale had the same minimum price and you only wanted one returned.

(internal query taken directly from @Michael Ames answer)