How can we retrieve last N rows inserted in the table

Posted on 2009-12-22
Last Modified: 2012-08-13
can anybody tell that how can we retrieve last N rows inserted in the table ?
PROBLEM - we have a table with suppose 30-40 lakh rows. we want to run a select query to retrieve data from only last 1 lakh rows that were inserted.
Question by:yadavricky
    LVL 2

    Expert Comment

    SELECT TOP 100000 column-list
    from table-name
    ORDER BY Timestamp or Identity column DESC
    here you can use either timestamp column or if the column has an identity column then can use it
    Please mark YES if this was helpful
    LVL 15

    Accepted Solution

    select * from (select * from table order by entered_dt desc) where rownum<=n ;
    LVL 15

    Assisted Solution

    by:Shaju Kumbalath
    SELECT *  FROM ( SELECT col1,col2, RANK() OVER (ORDER BY entered_dt desc) dt_rank
               FROM table )
    WHERE dt_rank <= n;
    LVL 4

    Expert Comment

    try this you can access last N rows :

    select top N ( select * from tablename1 order by id desc ) from tablename1
    LVL 31

    Expert Comment

    What is the structure of the table? Are there any fields with date or timestamp that would determine when the rows were entered? If not, what criteria would you use to determine "last"?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now