CODEWITHSUNDEEP
sqlsql-server
user12182753
user12182753

Reputation:

Select highest value based off of a different column

I am trying to get the highest value based off of another column. 

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

What I have is this

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

this is what I need

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

Upvotes: 0

Views: 52

Answers (3)

Gauravsa
Gauravsa

Reputation: 6514

You can simply do this:

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

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

This is too long for a comment.

Glad you found an answer that works with the GROUP BY. I would suggest you start using aliases in your queries. It can quickly and easily turn a wall of text into something fairly easy to see what is going on. You might end with something along these lines.

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

Upvotes: 0

Farhad Rahmanifard
Farhad Rahmanifard

Reputation: 688

You are just missing a GROUP BY clause in your query:

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

Upvotes: 1

Related Questions