Post reply

SELECT using MAX

  • November 17, 2007 at 1:06 am

    #98681

    This is an oversimplified example that should solve a question I have.

    IdSeq MyName KeyValue

    101 Ted 700

    102 John 701

    103 Mary 702

    104 Sue 700

    105 Brad 703

    106 Mike 704

    SELECT Name

    FROM MyTable

    WHERE KeyValue = 700

    AND ????IdSeq???

    I'd like to get the MAX(IdSeq) for the

    KeyValue of 700.

    Thanks

  • November 17, 2007 at 2:21 am

    #752050

    I haven't tested this, but even if it's not entirely syntactically correct, it'll point you in the right direction.

    SELECT m.IdSeq, m.MyName

    FROM MyTable m JOIN (

    SELECT MAX(IdSeq) AS IDS

    WHERE KeyValue = 700

    FROM MyTable) s

    ON m.IdSeq = s.IDS

    John

  • November 17, 2007 at 7:55 am

    #752060

    Looks good to me.

    --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)
    Intro to Tally Tables and Functions

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

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