Post reply

Taking only one record per ID

  • January 16, 2006 at 10:38 pm

    #167131

    Hi all,

    I am running a query on a database which returns more than one record per ID which is correct. But I would like to have only one record per ID, the one with the most recent LastUpdatedOn date. Is there a way of doing it with one query without using cursor.

    thanks anyway


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

  • January 16, 2006 at 10:46 pm

    #615050

    One approach would be to create a sub-query to obtain the most recent LastUpdatedOn date grouped by ID, e.g.

    select ID, MAX(LastUpdatedOn) as LastUpdatedOn from TableX group by ID

    Then simply join this select statement to your initial query to filter on the most recent LastUpdatedOn date. The join would be on both ID and LastUpdatedOn.

  • January 17, 2006 at 9:15 pm

    #615361

    Thanks a lot Paul. I have already found the same solution. Works perfect. Thnaks for input


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

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

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