CODEWITHSUNDEEP
phpmysql
Scott Brown
Scott Brown

Reputation: 73

How to get associated row data from min value

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.

Upvotes: 1

Views: 46

Answers (3)

xQbert
xQbert

Reputation: 35323

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

Upvotes: 0

The Impaler
The Impaler

Reputation: 48780

In MySQL 8.x you can do:

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

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)

Upvotes: 2

Related Questions