Post reply

Getting Last modified record from table

  • November 30, 2011 at 4:09 am

    #248747

    Hi all,

    IS it possible to get the id of last modified record from database table?

    If some one insert a new record ,then i can get its it by MAX(id) but what if some one have modified the middle record?

    so is it possible?

  • November 30, 2011 at 5:21 am

    #1414584

    You will have to maintain a seperate column(like ModifiedDate) and update it through a trigger. There is no other easy way to track it.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • November 30, 2011 at 6:17 am

    #1414613

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 30, 2011 at 6:25 am

    #1414618

    Jeff - that's a great idea.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 30, 2011 at 6:47 am

    #1414633

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    I have never heard about this idea ever. Great idea, Jeff. Learnt a new thing today for a common problem people come up with.

    Now I will think more than a couple of times before using an absolute statement like There is no other easy way to track it.🙂

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • November 30, 2011 at 7:32 am

    #1414675

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 30, 2011 at 7:41 am

    #1414682

    Sean Lange (11/30/2011)

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

    Just to add what Jeff has already said.

    Per MSDN:

    timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible.

  • November 30, 2011 at 7:55 am

    #1414695

    Sean Lange (11/30/2011)

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

    The main use for the ROWVERSION (TIMESTAMP) datatype is for an application to be able to detect and prevent concurrent updates by different users that would overwrite each others changes.

  • November 30, 2011 at 8:02 am

    #1414699

    Michael Valentine Jones (11/30/2011)

    Sean Lange (11/30/2011)

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    WOW. Now there is an actual reason to use this otherwise nearly useless datatype. 😛 Great idea!!!

    The main use for the ROWVERSION (TIMESTAMP) datatype is for an application to be able to detect and prevent concurrent updates by different users that would overwrite each others changes.

    I know the usage of the type but it is just isn't anything I need to deal with. I don't have anything with a large number of users all trying to edit the same data. I have never had a reason to use it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • December 4, 2011 at 9:40 am

    #1416420

    Now all we need to know is the true reason why someone would want to do this especially since, as Michael Valentine Jones pointed out, the last row updated could change in a heartbeat. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 20, 2012 at 12:25 am

    #1449088

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    This is good idea but i want to clear one thing.

    before this idea i was using like this(i know this was not good to get edited data)

    SELECT @RecordMAX=MAX(ID) FROM ABC

    UPDATE ABCSET x= @x WHERE ID=@RecordMAX

    (ID is primery key)

    Now i m using TIMESTAMP so my code is here

    SELECT @RecordMAX=MAX(TrackActivity) FROM ABC

    UPDATE ABC SET x= @x WHERE TrackActivity=@RecordMAX

    i am not sure that TrackActivity can be use a primery key?is this unique?i have doubt not to update wrong data.any help?

  • February 21, 2012 at 6:03 am

    #1449646

    Engr Shafiq (2/20/2012)

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    This is good idea but i want to clear one thing.

    before this idea i was using like this(i know this was not good to get edited data)

    SELECT @RecordMAX=MAX(ID) FROM ABC

    UPDATE ABCSET x= @x WHERE ID=@RecordMAX

    (ID is primery key)

    Now i m using TIMESTAMP so my code is here

    SELECT @RecordMAX=MAX(TrackActivity) FROM ABC

    UPDATE ABC SET x= @x WHERE TrackActivity=@RecordMAX

    i am not sure that TrackActivity can be use a primery key?is this unique?i have doubt not to update wrong data.any help?

    This is a classic concurrency problem. I don't think your code is not proper without some locking hints or serialiazable transaction isolation level (and enclosing transaction) due to there being the potential for an insert or delete between your SELECT and UPDATE.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 21, 2012 at 6:09 am

    #1449651

    TheSQLGuru (2/21/2012)

    Engr Shafiq (2/20/2012)

    Jeff Moden (11/30/2011)

    Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use the TIMESTAMP datatype. The last row inserted or modified in the table will always have the highest timestamp and I mean without a trigger.

    This is good idea but i want to clear one thing.

    before this idea i was using like this(i know this was not good to get edited data)

    SELECT @RecordMAX=MAX(ID) FROM ABC

    UPDATE ABCSET x= @x WHERE ID=@RecordMAX

    (ID is primery key)

    Now i m using TIMESTAMP so my code is here

    SELECT @RecordMAX=MAX(TrackActivity) FROM ABC

    UPDATE ABC SET x= @x WHERE TrackActivity=@RecordMAX

    i am not sure that TrackActivity can be use a primery key?is this unique?i have doubt not to update wrong data.any help?

    This is a classic concurrency problem. I don't think your code is not proper without some locking hints or serialiazable transaction isolation level (and enclosing transaction) due to there being the potential for an insert or delete between your SELECT and UPDATE.

    Do we have any solution on sqlserver side to solve concurrency problems?it will be better to have on sqlserver side.

  • February 21, 2012 at 7:04 am

    #1449680

    This particular one is easy:

    UPDATE ABCSET x= @x

    WHERE ID= (SELECT MAX(ID) FROM ABC)

    There are MUCH more difficult scenarios where concurrency will get you. Note this example assumes that @x comes from some different table than ABC and also that there is no concurrency issue with the "acquisition" of @x.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 21, 2012 at 10:44 pm

    #1450066

    @TheSQLGuru:

    But there is issue like when some one is updating middle record?for example max id is 10 but i want to edit id=5 then this query l not work?why we are using timestamp is for this....

    Actually i want to clear you about my question

    i am saving or updating records first time and then updating image field after this.both operations are not cary in one query...

Viewing 15 posts - 1 through 15 (of 15 total)

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