[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How can we retrieve last N rows inserted in the table

Posted on 2009-12-22
Medium Priority
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

Expert Comment

ID: 26103129
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

Shaju Kumbalath earned 200 total points
ID: 26103223
select * from (select * from table order by entered_dt desc) where rownum<=n ;
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 200 total points
ID: 26103237
SELECT *  FROM ( SELECT col1,col2, RANK() OVER (ORDER BY entered_dt desc) dt_rank
           FROM table )
WHERE dt_rank <= n;

Expert Comment

ID: 26103331
try this you can access last N rows :

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

Expert Comment

ID: 26104690
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"?

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month19 days, 10 hours left to enroll

873 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