如何获取Max值的id [英] How to get id of Max value

问题描述

我想要按月分组的最大平均值及其ID，

I want the max Average value and its id grouped by month,

Select FK_Transporter,Month, MAX(avg_rate) as BTPercent
FROM (
    SELECT kpi.FK_Transporter ,kpi.`Month`, AVG(kpi.OnTimeDelivery) as avg_rate
    FROM primarykpi as kpi,transporter as t
		WHERE kpi.FK_Transporter=t.Id and YEAR(kpi.`Month`)=2016
    GROUP BY FK_Transporter, MONTH(kpi.Month)) tp
GROUP BY tp.`Month`

这个查询给了我正确的平均值但是id是incor RECT。无法解决错误。这是此查询的输出

This query gave me right average but id is incorrect. Couldn't able to fing the mistake .Here is output of this query

FK_Transporter       Month             MaxAvg
1	                 2016-01-01	       2.270109534263611
1	                 2016-02-01	       0.8539329767227173
1	                 2016-03-01	       0.6764709949493408

FK_transporter是1 in所有记录都错了。我怎样才能解决这个问题。谢谢。

FK_transporter is 1 in all records whch is wrong . How can i fix this. Thanks.

推荐答案

SELECT * FROM data

WHERE temp_hi =（SELECT MAX（temp_hi）FROM FROM）

SELECT * FROM data
WHERE temp_hi = (SELECT MAX(temp_hi) FROM data)

这篇关于如何获取Max值的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！