Post reply

Find Max Unique Value

  • October 3, 2009 at 7:09 pm

    #134276

    I have inherited a database application that takes tests from users and tracks the results. Users have a unique ID, but users are allowed to retake tests as many times as they would like. I need to find a way to pull the max Test Date, and remove the rest of the records from a copy of the results table. This will be used for reporting the latest test results. I am trying to write a stored procedure to do this, but I am having an issue getting the select statement correct.

    The fields I have available are:

    ClientID

    CountyID

    HighestGradeCompleted

    LaborForceStatus

    Gender

    AgeOnApp

    TestID

    Score

    TestDate

    CertificateID

    CityID

    ResultID

    I am trying to get the latest test result by client, by test, and pull all other relevant information.

    Doing something like Max(TestDate) will only pull me the max record for whatever I have in my group by statement. Thus, if a client scores higher the second time around, I still have two records.

    Suggestions?

  • October 3, 2009 at 7:39 pm

    #1061402

    Is this what you want?

    WITH cteMax AS

    (

    SELECT ClientID,TestID,MAX(TestDate) AS MaxTextDate

    FROM dbo.YourTable

    GROUP BY ClientID,TestID

    )

    SELECT yt.*

    FROM dbo.YourTable yt

    INNER JOIN cteMax mx

    ON yt.ClientID = mx.ClientID

    AND yt.TestID = mx.TestID

    AND yt.TestDate = mx.MaxTextDate

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2009 at 8:23 pm

    #1061406

    That will work! Thank you very much.

  • October 3, 2009 at 9:36 pm

    #1061417

    You bet... thank you for the feedback. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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