Identifying field value corresponding to MAX of another field value

  • daniness

    SSCrazy

    Points: 2890

    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!

  • drew.allen

    SSC Guru

    Points: 76735

    Why do you have parens around your query?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • daniness

    SSCrazy

    Points: 2890

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

  • ZZartin

    SSC-Dedicated

    Points: 30414

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

  • daniness

    SSCrazy

    Points: 2890

    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: 442332

    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: 442332

    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

    Right there with Babe

    Points: 796

    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

    Right there with Babe

    Points: 796

    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: 10246

    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

    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!

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

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