View Poll Results: How did you find the problem?

Voters
0. You may not vote on this poll
  • Stupid

    0 0%
  • Easy

    0 0%
  • Normal

    0 0%
  • Tough

    0 0%
Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Question Unanswered: How to retrieve Last n inserted records from an un-indexed table

    Suppose I have table with the following structure -

    Create table TblInfo(Name varchar(20), Email varchar(30))

    Note that I have not put any constraints or setup any relationship on this table. No index No primary key.

    Now I insert 5 records into this table

    insert into TblInfo('abc', 'abc@dbforums.com')
    insert into TblInfo('def', 'def@dbforums.com')
    insert into TblInfo('ijk', 'ijk@dbforums.com')
    insert into TblInfo('lmn', 'lmn@dbforums.com')
    insert into TblInfo('opq', 'opq@dbforums.com')


    What will be the sql query to retrieve Last 3 inserted records?

    Above is just a scenario I created for illustrating the problem. The generic question I have is - How to retrieve Last n inserted records from an un-indexed table (having no primary key or index)?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    adonisaseem, I think you have the wrong idea about how a relational database works. When rows are added to a table, they can be put on any page in any order.

    With the design of your table, there is no way to know when rows were added or in what order. Even if you did a Select over a multi-row insert, all the Inserted rows would be displayed and not just the last 3 (assuming more than 3 rows were Inserted at one time).

    Since neither Name nor Email address have anything to do with Insert order, Indexes won't help any.

    What you need is something that does keep track of the Insert order. This can be a Timestamp column or an Identity column (or even a column that you increment manually).

    Once you have this, something like would retrieve the last 3 rows entered:

    SELECT TOP 3 column-list
    from table-name
    ORDER BY Timestamp or Identity column DESC

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Identity columns do not determine order of insertion.
    Code:
    Create table TblInfo(Name varchar(20), Email varchar(30), when_created datetime DEFAULT GetDate())
    Code:
    SELECT TOP 3 WITH TIES
            *
    FROM  TblInfo --please don't prefix your object names!
    ORDER
        BY when_created DESC
    George
    Home | Blog

  4. #4
    Join Date
    Jul 2009
    Posts
    5
    Thanks Stealth_DBA for reply. I know by using identity field or timestamp we can get the desired result. I was wondering if sql server internally maintains such information. so you say it does not. I know oracle has something called rowid, can it be used for finding last entries without using identity or timestamp in oracle? Any idea?

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SQL Server records information about the insertion order in the log. Using the Change Data Capture feature it is possible to retrieve that information
    Change Data Capture

    Oracle has a similar feature called Flashback.

    Oracle's ROWID doesn't necessarily retain the insertion order. As George has already said, neither does IDENTITY in SQL Server.

  6. #6
    Join Date
    Jul 2009
    Posts
    5
    Correct me if I am wrong -

    There is no sql query which can show me last n rows inserted in a table like this - Create table TblInfo(Name varchar(20), Email varchar(30))
    without altering it

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Assuming you have CDC enabled you could do it like this (untested code):

    SELECT TOP (@n) Name, Email,
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) CreationDate
    FROM cdc.dbo_TblInfo_CT
    WHERE __$operation = 2
    ORDER BY CreationDate DESC;

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How's about renaming the table, adding an extra datetime column, then creating a view on the changed table with the orignal name, which only contains those two columns?

    I've had to perform similar fudges before to make a change to an existing application without the FE "knowing".
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •