根据不同的列选择最高值

Question

我正试图根据另一列获得最高值。

SELECT DISTINCT 
AppDetailVehicleValuation.AppID, 
VehicleValuationOption.Description, 
MAX (VehicleValuationOptionValueType.Value)
FROM            
AppDetailVehicleValuation
INNER JOIN VehicleValuationOption 
ON AppDetailVehicleValuation.ValuationID = VehicleValuationOption.ValuationID 
INNER JOIN VehicleValuationOptionValueType 
ON VehicleValuationOption.ValuationOptionID = VehicleValuationOptionValueType.ValuationOptionID
WHERE        
(VehicleValuationOption.IsSelected LIKE '1') 
AND (VehicleValuationOption.IsSystemOption LIKE '1')

我有这个

AppID | Description | Value
999     Beats Audio   425.00
999     Beats Audio   475.00
999     Power Str.    600.00
999     Power Str.    750.00

这就是我需要的

AppID | Description | Value
999     Beats Audio   475.00
999     Power Str.  | 750.00

Answer 1

您的查询中缺少GROUP BY子句：

SELECT
AppDetailVehicleValuation.AppID, 
VehicleValuationOption.Description, 
MAX (VehicleValuationOptionValueType.Value)
FROM            
AppDetailVehicleValuation
INNER JOIN VehicleValuationOption 
ON AppDetailVehicleValuation.ValuationID = VehicleValuationOption.ValuationID 
INNER JOIN VehicleValuationOptionValueType 
ON VehicleValuationOption.ValuationOptionID = VehicleValuationOptionValueType.ValuationOptionID
WHERE        
(VehicleValuationOption.IsSelected LIKE '1') 
AND (VehicleValuationOption.IsSystemOption LIKE '1')
GROUP BY AppDetailVehicleValuation.AppID, VehicleValuationOption.Description

Answer 2

您可以简单地执行以下操作：

SELECT
t.AppId, 
t.Description, 
max(t.Value)
FROM mytable t
GROUP BY t.description, t.AppId

Answer 3

这对于注释来说太长了。

很高兴您找到了一个适用于GROUP BY的答案。我建议您在查询中开始使用别名。它可以快速而轻松地将一堵文本墙变成一些很容易看到正在发生的事情。你可能会以这样的方式结束。

SELECT advv.AppID
, vvo.Description
, MaxValue = MAX(vvot.Value)
FROM AppDetailVehicleValuation advv
INNER JOIN VehicleValuationOption vvo ON advv.ValuationID = vvo.ValuationID 
INNER JOIN VehicleValuationOptionValueType vvot ON vvo.ValuationOptionID = vvot.ValuationOptionID
WHERE vvo.IsSelected = '1'
AND vvo.IsSystemOption = '1'
group by advv.AppID
, vvo.Description