FailedUnitTest FailedUnitTest - 6 months ago 8
SQL Question

Column invalid in the select list, not contaned in aggregate function

I would like to display columns like this:

ProductID | Price | PricingPlanLicense | PricingPlanQuality
----------+-------+--------------------+-------------------
1 16.99 Buy HD
1 12.99 Buy SD
1 2.99 Rent HD
3 4.99 Rent HD


The problem is that in my data I have multiple
PricingPlanLicense
and
PricingPlanQuality
combinations and I would like to take the one with the highest price.

So far I have this query:

SELECT
ProductID, MAX(Price), PricingPlanLicense, PricingPlanQuality
FROM
[#ExportPricingTable]
GROUP BY
PricingPlanLicense, PricingPlanQuality


But this doesn't work because
ProductID
is not aggregated, how can I tell SQL Server to use the
ProductID
that is already there?

Thanks

Answer

Add ProductID to the Group By clause.

SELECT ProductID, MAX(Price), PricingPlanLicense, PricingPlanQuality
FROM [#ExportPricingTable] 
GROUP BY ProductID, PricingPlanLicense, PricingPlanQuality