locked
Select the last records inserted to table RRS feed

  • Question

  • Hi

    Is there a way to select the last recorder inserted to a table - where there is no other column that can give this information?

    I searched and found this post  where someone has suggested the following command. 

     

    Here is a standard method for SQL Server 2005/2008. This will perform well if you have a covering index on {item, date, 
    value}.
    
    
    SELECT item, date, value
    FROM (
    SELECT item, date, value,
        ROW_NUMBER() OVER(PARTITION BY item ORDER BY date DESC) AS rk
    FROM Items) AS I
    WHERE rk = 1;
    
    
    

     

    As I understand this query it actually relay on the  date column. So the recorded with the latest date will get rownum 1.

    This is because of the order by clause. If this is correct I wouldn't need this query in fist place - i already have the date.  

    Am I wrong?

    what about the note about the index  before the statement?

     

    tx

     

    Oren


    Tx Oren
    Wednesday, March 16, 2011 8:59 PM

Answers

  • No, without a special column that will dictate the sort, there is nothing in SQL Server (except for checking the transaction logs) that will tell us which record was inserted last into the table.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Gert-Jan Strik Saturday, March 19, 2011 11:36 AM
    • Marked as answer by Kalman Toth Wednesday, March 23, 2011 3:10 AM
    Thursday, March 17, 2011 12:55 PM
  • Hi All

    Thanks for your comments

    I understand that in case one wants to track the last inserted row it actually must be managed by an some kind of applicative column, SP, trigger etc.

    tx

    Oren


    Tx Oren
    • Marked as answer by Jammusi Wednesday, March 23, 2011 4:18 PM
    Wednesday, March 23, 2011 4:18 PM

All replies

  • The query above returns latest item per each item. If you need to return just one latest record and you do use dates in your table to put the datetime at the time of insertion, then

    select top 1 * from myTable order by Date DESC
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 16, 2011 9:03 PM
  • Stop programming and read a book on RDBMS. Near the front of the book --any RDBMS book --there is a section on Dr. Codd's 12 rules. Read the Information Principle.  Tables have no ordering. All relationships are shown as scalar values in the columns of tables.  So where is the insertion timestamp?

    Asking this question tells us that you have not done your basic education.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, March 16, 2011 9:12 PM
  • Hi,

            If you are using identity coulmn then use @@identity which will give last inserted values in table. For more info read given link.

    http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

     


    Nikunj Nandaniya
    Thursday, March 17, 2011 5:15 AM
  • Hi Jammusi, Thnx for ur question..

    As I understand, u r looking for an answer for .. if there is no identity column , no date column, no other column by which we can find last inserted row.

    Im also searching for the answer.

    I think, we should go inside any index of the table. index maintain the key with new insertion of the row. So what can be the way to find that last inserted key in the index?

     

     


    --------------------------------- Devender Bijania
    Thursday, March 17, 2011 5:43 AM
  • If there is any clustered index on ur table. clustered index sorts data physically. So simple select command on the index column , can give the order of insertion.

     


    --------------------------------- Devender Bijania
    Thursday, March 17, 2011 5:46 AM
  • I dont know. I was aiming to something internal to SQL server - if exists.

    Something like ROWID in Oracle

    If i have any "applicative" column - then i have no problem in the first place...

     

    Clustered index might be helpful here...anly i am not sure what "physical" exactly means.

    does it mean that the last record inserted will always be last in order - even if the value in the column being indexed should be in the middle(for simple example: D inserted last, whereas A and Z are already in.

    tx

    Oren

     


    Tx Oren
    Thursday, March 17, 2011 8:36 AM
  • No, without a special column that will dictate the sort, there is nothing in SQL Server (except for checking the transaction logs) that will tell us which record was inserted last into the table.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Gert-Jan Strik Saturday, March 19, 2011 11:36 AM
    • Marked as answer by Kalman Toth Wednesday, March 23, 2011 3:10 AM
    Thursday, March 17, 2011 12:55 PM
  • Jammusi,.

    Only way to find out such data is to look at the log tables.

    refer the link for a similar ask. http://sqlserver2000.databases.aspfaq.com/can-sql-server-tell-me-which-row-was-inserted-most-recently.html

    • Proposed as answer by Naomi N Wednesday, March 23, 2011 3:41 AM
    Friday, March 18, 2011 6:01 AM
    • Edited by Kalman Toth Monday, October 1, 2012 12:36 AM
    Wednesday, March 23, 2011 3:12 AM
  • Hi All

    Thanks for your comments

    I understand that in case one wants to track the last inserted row it actually must be managed by an some kind of applicative column, SP, trigger etc.

    tx

    Oren


    Tx Oren
    • Marked as answer by Jammusi Wednesday, March 23, 2011 4:18 PM
    Wednesday, March 23, 2011 4:18 PM
  • Thank you Naomi,
    I needed to use it as a variable. With your help, here is what I came up with.
    DECLARE @varLastTrans int;
    SET @varLastTrans = (SELECT TOP 1 [transactionID]
    FROM aTransactionTable
    ORDER BY transactionID DESC
    );
    


    PrehistoricBrainTrysHandAtProgramming
    Monday, July 11, 2011 7:30 AM