I'm hoping to get your expertise on this. I'm trying to populate a temp table as part of a bigger query, and I need to identify the value for the AssetClassCode which has the corresponding Max(AssetValue). I'm using the below script, but for some reason, it's returning an "Invalid syntax near 'ORDER'. Expecting ')', EXCEPT, or UNION" error and if I exclude the ORDER BY clause from the closing parenthesis, the error goes away, but the order of the MaxAssetValue is not correct.
IF OBJECT_ID('tempdb.dbo.#TempAssetClassDetails') IS NOT NULL
DROP TABLE dbo.#TempAssetClassDetails;
(SELECT mac.code AS AssetClassCode, eal.PCMAssetClassId, e.id, et.enrollmentcoveragePeriodID AS CoverageID, MAX(ea.AssetValue) AS MaxAssetValue
FROM SESEnrollmentINT.dbo.EnrollmentAsset ea
INNER JOIN SESEnrollmentINT.dbo.EnrollmentAssetList eal ON ea.EnrollmentAssetListId = eal.Id
INNER JOIN SESEnrollmentINT.dbo.EnrollmentTrans et ON eal.EnrollmentTransId = et.Id
INNER JOIN SESEnrollmentINT.dbo.EnrollmentCoveragePeriod ecp ON et.EnrollmentCoveragePeriodId = ecp.Id
INNER JOIN SESEnrollmentINT.dbo.Enrollment e ON ecp.EnrollmentId = e.Id
INNER JOIN SESHostInt.dbo.MasterAssetClass mac ON eal.PCMAssetClassId = mac.Id
GROUP BY mac.Code, eal.PCMAssetClassId, e.Id, et.enrollmentcoveragePeriodID, ea.AssetValue
ORDER BY MAX(ea.AssetValue) DESC)
Here's a snapshot of some records where you can see the MaxAssetValue isn't being ordered properly:
I appreciate your guidance on this. Thanks!