Identifying field value corresponding to MAX of another field value

Question

Post reply

Identifying field value corresponding to MAX of another field value

daniness

SSCrazy

Points: 2890
More actions
September 26, 2018 at 1:40 pm

#415734

Hey All,
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;
GO
(SELECT mac.code AS AssetClassCode, eal.PCMAssetClassId, e.id, et.enrollmentcoveragePeriodID AS CoverageID, MAX(ea.AssetValue) AS MaxAssetValue
INTO #TempAssetClassDetails
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
--HAVING MAX(MaxAssetValue))
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!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

Why do you have parens around your query?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

daniness SSCrazy Points: 2890 More actions · Answer 2

Good point, Drew. I removed the parens, but it's still not ordering by correctly. Any thoughts? Thanks.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

What's not ordering correctly? The query or when you select from the temp table?

daniness SSCrazy Points: 2890 More actions · Answer 4

ZZartin - Wednesday, September 26, 2018 2:52 PM
What's not ordering correctly? The query or when you select from the temp table?

Good question......it actually appears that selecting from the temp table isn't ordering the MaxAssetValue column properly. I'm not sure why though.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

daniness - Wednesday, September 26, 2018 2:57 PM
ZZartin - Wednesday, September 26, 2018 2:52 PM
What's not ordering correctly? The query or when you select from the temp table?
Good question......it actually appears that selecting from the temp table isn't ordering the MaxAssetValue column properly. I'm not sure why though.

What is the code you are running to select the data from the temp table?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

daniness - Wednesday, September 26, 2018 1:40 PM
Hey All,
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;
GO
(SELECT mac.code AS AssetClassCode, eal.PCMAssetClassId, e.id, et.enrollmentcoveragePeriodID AS CoverageID, MAX(ea.AssetValue) AS MaxAssetValue
INTO #TempAssetClassDetails
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
--HAVING MAX(MaxAssetValue))
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!

What does the data look like if you run it with the INTO #TempAssetClassDetails commented out?

sathwik.em91 SSC Eights! Points: 878 More actions · Answer 7

daniness - Wednesday, September 26, 2018 1:40 PM
Hey All,
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;
GO
(SELECT mac.code AS AssetClassCode, eal.PCMAssetClassId, e.id, et.enrollmentcoveragePeriodID AS CoverageID, MAX(ea.AssetValue) AS MaxAssetValue
INTO #TempAssetClassDetails
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
--HAVING MAX(MaxAssetValue))
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!

sathwik.em91 SSC Eights! Points: 878 More actions · Answer 8

Sorry for earlier post
Do you need to group by ea.Asset Value??If not please remove it and run your code.

Thanks
SE

Joe Torre SSChampion Points: 10248 More actions · Answer 9

Add a Row_number() OVER(PARTITION BY mac.code ORDER BY ea.AssetValue DESC) Rn to the select list in the derived table and add a where clause WHERE Rn = 1

daniness SSCrazy Points: 2890 More actions · Answer 10

Hey All,

Thanks for your input. I was able to get some help on this and ended up using a cte:

unique_results AS (

SELECT ID, --AssetClassDescription,

CoverageID,

MAX(Code) AS Code,

AssetValue

FROM AssetClassCode_by_Enrollments

WHERE CONCAT(ID, CoverageID, AssetValue) IN (SELECT CONCAT(ID, CoverageID, MAX(AssetValue)) AS AssetValue

FROM AssetClassCode_by_Enrollments

GROUP BY ID

,CoverageID)

GROUP BY ID,

CoverageID,

AssetValue)

SELECT ID

,CoverageID

,Code

,AssetValue

,(SELECT MAX(ISNULL(Description, 'not found in MasterAssetClass table'))

FROM SESHostProdCopy.dbo.MasterAssetClass mac

WHERE mac.Code = unique_results.code) AS AssetDescription

INTO #TempAssetClassDetails_CS2

from unique_results

order by ID;

SELECT DISTINCT

ire.SourceId AS EnrollmentID

,ire.SourceCoveragePeriod AS CoverageID

,ip.PolicyNumber

--,re.SourceTransactionId

,ip.PolicyCode

,ip.PCMPolicyID

,ip.ProductName

,pp.Code

,ire.ContractNumber --CustomerAccountNumber

,psc.Name AS CoverageTypeName

,TACD.Code AS AssetClassCode

,TACD.AssetDescription AS AssetClassDescription

... FROM...

This seemed to have done the trick. Thanks for your replies!