Finding the last updated record

  • 10 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









  • 10 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; 
  • 10 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“A computer is a stupid machine with the ability to do incredibly smart things, while computer programmers are smart people with the ability to do incredibly stupid things. They are, in short, a perfect match” - Bill Bryson