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 p...

问题:

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. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除