How to get associated row data from min value

Question

I am trying to get the minimum price (totalprice) from a grouped column (sku) and then get the corresponding supplier data (supplier) associated with the resulting lowest price.

Table example

sku     supplier    totalprice
505     Sup1        20
505     Sup2        30
505     Sup3        25
605     Sup1        100
605     Sup2        97
605     Sup3        111

I am trying to group the sku column and get the lowest price and related supplier,

$query  = "SELECT sku, supplier,  MIN(totalprice) FROM pricetable GROUP BY sku";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_array($result)) {
    echo "The cheapest  ". $row['sku']. " is £" .$row['MIN(totalprice)']." from ".$row['supplier'];

For example,

group 505 has the lowest price of 20 from supplier Sup1

group 606 has the lowest price of 97 from supplier Sup2

The code above gives me the lowest price for each sku group, but I can't get the associated supplier name with it.

Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — sticky bit, Jan 23 '19 at 13:31

score 2 · Answer 1 · answered Jan 23 '19 at 13:31

2

You can try below - using correlated subquery

SELECT sku, supplier, totalprice FROM pricetable t1
where totalprice in 
    (select MIN(totalprice) from pricetable t2 where t1.sku=t2.sku GROUP BY t2.sku)

answered Jan 23 '19 at 13:31

Fahmi

37,315
5
22
31

1

I think your solution is better than mine. It works in [the old now] MySQL 5.x. Upvoting. – The Impaler Jan 23 '19 at 13:34
Hi, thanks I tried that but it only gives me the lowest price for sku 505, nothing for the next sku 605, I have kept my (while loop) I assume I don't need to change that? – Scott Brown Jan 23 '19 at 13:55

score 0 · Answer 2 · answered Jan 23 '19 at 13:32

0

In MySQL 8.x you can do:

select *
from pricetable
where rank() over(partition by sku order by totalprice) = 1

answered Jan 23 '19 at 13:32

The Impaler

45,731
9
39
76

score 0 · Answer 3 · answered Jan 23 '19 at 13:40

Another common way to do this is to create a derived table with the sku and the min totalprice per sku and join back to your base dataset.

SELECT PT.sku, PT.supplier,  PT.totalprice
FROM pricetable PT
INNER JOIN (SELECT min(totalprice) MTP, SKU 
            FROM pricetable 
            GROUP BY sku) B
 on PT.SKU = B.SKU
and PT.MTP = PT.totalprice

How to get associated row data from min value

3 Answers3

Linked