Post reply

Selecting row with max date from table with multiple rows

  • December 5, 2007 at 8:09 am

    #61472

    I have a table, TableA, with columns A_Acct, B, C, D....W, A_Date. There are multiple rows per A_Acct with different A_Date values. I need to select out all the columns in TableA for the row for A_Acct with the most recent (highest) value of A_Date. I've been trying to wrap my head around how to do this using Set methods using derived queries, etc and I'm stuck.

    Any help would be appreciated.:w00t:

     

  • December 5, 2007 at 8:17 am

    #757349

    SELECT A.*

    FROM

    Table1 A

    JOIN

    (

    SELECT A_Acct, MAX(A_Date) A_Date

    FROM

    Table1

    GROUP BY

    A_Acct

    ) MaxDate

    ON

    A.A_Acct = MaxDate.A_Acct

    ANDA.A_Date = MaxDate.A_Date

    Regards,
    gova

  • December 5, 2007 at 8:18 am

    #757350

    I was just coming back to post that I'd arrived at that same solution. Apparently a moment of clarity.

    Thanks.

     

  • December 5, 2007 at 8:22 am

    #757352

    Without table schema, sample data or required output, I'm taking a guess here.

    SELECT A_Table.A_Acct, B, C, D....W, A_Date

    FROM A_Table inner join

    (SELECT A_Acct, MAX(A_Date) as LatestDate

    FROM A_Table Group By A_Acct) Latest

    ON A_Table.A_Acct = Latest.A_Acct and A_Table.A_Date = Latest.LatestDate

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 5, 2007 at 9:06 am

    #757370

    Yes. THat was the approach I took.

     

Viewing 5 posts - 1 through 4 (of 4 total)

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