Finding the last updated record

• • Reply

  13 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 years ago
  Hi All,
  
  I have a problem.
  When my user updates his/her profile, I have a stored proc which updates a datetime field, which contains the date the profile was last modified.
  What I now need is a way using t-sql that I can pull the row from the table, where the datetime field is closest to the current system time.
  
  Anyone have any ideas how I could do this?
  And also what possible implications could their be if 2 or more users were to update their profile at the exact same time?
  
  Kind Regards
  Mick
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  13 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 years ago

  I figured it:
  
  

  SELECT TOP 1 * FROM table_name WHERE modified_datetime_column_name < CURRENT_TIMESTAMP ORDER BY modified_datetime_column_name DESC; 

  Report abuse
• • Reply

  13 years ago

  by Mistuke

  Mistaken Luke

  The Netherlands, Netherlands

  Joined 18 years ago

  Aye, to things about your solution, 1 it'll always return just 1 user, no matter if multiple users updated their profile at once. 2. all the dates will be < current_timestamp, since they were all made in the past, not the future (unless they can time travel) so that statement is overkill.

  i would rephrase the query to SELECT * FROM table_name WHERE modified_datetime = (SELECT TOP 1 modified_datetime FROM table_name ORDER BY modified_datetime DESC;);

  subquery returns last date and then main query returns all records matching that date

  Report abuse