# 问题:

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.

# 回答1:

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

# 回答2:

In MySQL 8.x you can do:

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

# 回答3:

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
``````
• 发表于 2019-01-28 22:09
• 阅读 ( 60 )
• 分类：网络文章

1. 小编 文章