Selecting records based on last updated date/time field

• bdgjcd

  SSC Rookie

  Points: 33

  More actions

  November 20, 2009 at 7:27 am

  #385822

  Probably a simple question but new to this and struggling, so any help appreciated.

  I have a table which contains multiple records for each person, each record has a last updated field containing a datetime value.

  I need to create a report showing the person name and several other fields but only those records which correspond to the most recently updated record as defined by the last updated field.

  i.e. from the following data: -

  Name Location Colour DateTime

  Fred New York Blue 10/21/2009 11:02:03

  Fred Boston Red 10/27/2009 14:15:47

  Fred Chicago Green 10/24/2009 16:13:27

  My report would show - Fred Boston Red 10/27/2009 14:15:47 - as the last updated record.

  Any suggestions??

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 20, 2009 at 7:36 am

  #1082222

  Give this a shot:

  with LatestInfo as (

  select

  [Name],

  max([DateTime]) as [DateTime]

  from

  dbo.MyTable

  group by

  [Name]

  )

  select

  mt.*

  from

  dbo.MyTable mt

  inner join LatestInfo li

  on (mt.[Name] = li.[Name]

  and mt.[DateTime] = li.[DateTime]);

• bdgjcd

  SSC Rookie

  Points: 33

  More actions

  November 20, 2009 at 8:05 am

  #1082251

  Thanks Lynn

  Seems to be working ok (not sure why - but can figure that out I guess) just need to validate the data it's giving me.

  Thanks a million for the help:-D