Thread: select max

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    surrey
    Posts
    2
    Rep Power
    0

    Unhappy select max


    Anybody know how to get the last entry in a row depending on the max number of another entry in the same table. These are the 2 tables:

    class :
    classId
    className

    status:
    statusId
    classId
    ...

    Problem: i have a number of same classId in status table but i only want the one that 1. matches the highest statusId number and 2. matches a classId entry in the class table

    I can only seem to get all of the classId back in statusId that match class classId.

    selecting max(classId) in a subquery doesn't seem to work?
    Is my thinking wrong - can this be done by comparing classId in 2 tables and a subquery for the max??

    It must be straightforward, but i'm stumped!

    anyone?
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    Code:
    SELECT classId, MAX(statusId)
    FROM    Status WITH (NOLOCK)
    GROUP BY classId
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    surrey
    Posts
    2
    Rep Power
    0
    Works great, thanks for that.

IMN logo majestic logo threadwatch logo seochat tools logo