How to pull the bigger of 2 numbers in the same field?

  • In the testscores table, i am first looking for people with a test_type of 'lsatot' IF, they dont have that, then they would have a type of either 'lsatavg' or 'average' which is fine. BUT, some people have 2 record types of 'lsattot' or 'lsatavg' or 'average' how would i pull the bigger of the 2 numbers IF they had multiple reocords of the same type?

    So for example.

    John doe might have a 'lsatot' record type with an associated score of 150, he has an additonal 'lsattot' record type with an assosciated score of 167. I want it to pull the higher number. Does this make sense? This is what i have so far, but its pulling every value, and not just the highest one. FYI i had a provided list of id's that i am comparing to the id's in the database and thats what im using the exists part.

  • FYI the raw_score field is the one i want to pull and is directly related to the record type.

  • Craig,

    This would be a lot easier to test code for if you provided some data and table schema info. See the following for how to do that effectively...

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    --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 3 posts - 1 through 2 (of 2 total)

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