Post reply

select record based on latest update date

  • April 6, 2006 at 3:28 am

    #112477

    i have one table

    id  class date

    1     1     2/mar/2005

    1     2      2/mar/2006

    2      4     2/mar/2006

    2     3      2/mar/2003

    3      2     2/mar/2005

    4       1    2/mar/2002

    4       2    2/mar/2003

    now i want only records with latest date

    1     2      2/mar/2006

    2      4     2/mar/2006

    3      2     2/mar/2005

    4       2    2/mar/2003

    without using subquery

     

  • April 6, 2006 at 3:58 am

    #631161

    Is the latest date always going to have the highest class?

    If so:

    SELECT id, MAX(class), MAX(date)

    FROM table

    GROUP BY id

    If that rule doesn't hold though, you'll get spurious results.

  • April 6, 2006 at 4:04 am

    #631163

    You need the max date... per class i suppose?

    What if for tha same class you have 2 or more rows with same max date?

    Please.... be more specific, even the result seems to be obvious.

    ------------
    When you 've got a hammer, everything starts to look like a nail...

  • April 6, 2006 at 6:51 am

    #631196

    I understand the question but why must it be without a sub-query?  Seems like a very unnecessary limitation unless you're doing this in an early version of MySQL or something similar.

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