lundi 29 juin 2015

SELECT MIN and MAX of column1 by another distinct column2 and fetch entire row

Ok, so I have the table Television that has over 1,000 records and looks like this:

ID     Code    Source   Brand       Price
-----------------------------------------
930    A584    C11      Panasonic   512
843    VG873   U19      Sony        590
301    A584    J63      Panasonic   494
738    D900    T32      Samsung     378
786    VG873   Y91      Sony        575
409    E764    G48      LG          435
912    VG873   Y91      Sony        535
626    E764    H14      LG          460
581    E764    C55      LG          455
557    D900    I42      Samsung     390

I'm trying to run a query that would fetch the lowest price from each distinct brand where price is greater than or equal to $400, fetching the entire row. The result on the above example set should look like this:

ID     Code    Source   Brand       Price
-----------------------------------------
301    A584    J63      Panasonic   494
409    E764    G48      LG          435
912    VG873   Y91      Sony        535

I tried some answers of somewhat similar questions in here but the results were off by a mile. The last I tried is the following and it was the closest but still not giving the desired result:

SELECT tv.* FROM Television tv
    INNER JOIN (SELECT Brand, MIN(Price) AS MinPrice 
    FROM Television
    GROUP BY Brand) groupedTV
ON tv.Brand= groupedTV.Brand
AND tv.Price= groupedTV.MinPrice
WHERE tv.Price>=400

Any help is appreciated.

Edit: Corrected the result set (ID 301 should be the lowest in Panasonic). Thanks to @wilfo.

Aucun commentaire :

Enregistrer un commentaire