Selecting row with max date from table with multiple rows

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  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:

   
  
  

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  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

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  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.

   
  
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  December 5, 2007 at 9:06 am

  #757370

  Yes. THat was the approach I took.