How can I get recent 10 records out of 100 records

HI all
table contain some 1000 records, today I inserted 100 records into table, so I want first 10 records data from 100 records
how can I retrieve

Questions by vinay.juri

Showing Answers 1 - 19 of 19 Answers

The problem with the question is it is too vague.

How are you ordering the inserts because if the inserts were done in a batch, all 100 records at once, then all 100 records will be "most recent". You could just as well randomly pick any 10 records from the 100 inserted records and claim that random selected 10 was the "most recently" inserted records.

Equally, rownum is meanless since rownum returns a number indicating the order in which Oracle selects the row from a table, not the order it was insert.

You can not reliably control the order Oracle stores the rows inserted in a table, even if they seem to be inserted sequentially.

If you inserted the same 100 records again, the 10 "most recent" or the 1st 10 records via rownum might not be the same as the previous 100 records insert/(10 most recent or 1st 10) records - even if the data in both cases was the same.

Assuming you had inserted the records in such a way that some column, call it column "ord", contained the ordinal record of inserts such a way that ord = 1 was the 1st record inserted and ord = 100 was the last record inserted then:

Code
  1. SELECT t.*

  2. FROM <table name> t

  3. WHERE t.ord < 11

  4.  

  5. would give you the 1st 10 inserted records, AND :

  6.  

  7. SELECT t.*

  8. FROM <table name> t

  9. minus

  10. SELECT t.*

  11. FROM <table name> t

  12. WHERE t.ord < 91


would give the 10 most recently inserted records.

  Was this answer useful?  Yes

Susil Kumar Nagarajan

  • Jan 21st, 2012
 

Analytic function will serve the purpose. See below SQL.

Code
  1. SELECT *

  2. FROM

  3.   (SELECT *

  4.   FROM

  5.     (SELECT rowid,

  6.       e.*,

  7.       rank() over (ORDER BY rowid ASC) rowid_rank

  8.     FROM employee e

  9.     )

  10.   WHERE rowid_rank >

  11.     (SELECT COUNT(1)-100 FROM employee

  12.     )

  13.   ORDER BY 1 ASC

  14.   )

  15. WHERE ROWNUM < 11;

  16.  

  Was this answer useful?  Yes

sajal

  • Feb 9th, 2012
 

Code
  1. SELECT * FROM

  2.          (SELECT rowid,e.*,rank() over(ORDER BY rowid DESC) AS rank FROM emp e )

  3.          WHERE rank<=10 ORDER BY rowid

Balapradee Reddy

  • Feb 14th, 2012
 

Code
  1. SELECT * FROM (SELECT * FROM table_name ORDER BY dbms_random.value) WHERE rownum <= 10;

  Was this answer useful?  Yes

Mohammad Sarwar

  • Feb 17th, 2012
 

select * from (select * from emp e order by rowid desc) where rownum<=10

  Was this answer useful?  Yes

The following is basicly a pharse of a responses given by Thomas Kyte, from the Blog "Ask Tom Oracle?", on a similar topics. The comments by Tom highlight the problem with thinking Rowid and/or Rownum - which is being used most of the solutions to this question - will not be the answer.

What is your definition of the "first 10 records of data"?

You know, the "first 10 records of data" inserted may very well be the first 10 records returned by "select
* from t", it may be the 100th thru 109th, it may be the 1000th thru 1009th, it may in fact be ANY 10 records.

The "first 10 records of data", there isnt such a concept. There is no such thing as "first 10 records of data" or "last 10 records of data" there are just rows.

Records are not returned in order of anything. You would have to SORT the data in order to have a "first" and "last".

If you want the "first 10 records of data" inserted, you need to have a timestamp or sequence number
assigned to each record as they are inserted, then you will get the "first 10 records of data". It is the ONLY way.

You MUST have some column that you can order by.

Rowid - wont work. The rowid is not "generated" in as much as "derived", it is the address of the row on disk.
Rowids are not "sequential" things, they say WHERE a row exists, not when a row was inserted or anything like that. Since space can and will be reused, rowids can and will be "reused". A rowid implies NOTHING other than the location of the row physically. It does not imply age, order of insertion, or anything like that.

Rownum -- doesnt work either, it just doesnt work. Rownum is a psuedo column, its value is assigned as rows are output.

Reference:
"Fetching last record from a table"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:912210644860

"Rownum"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:948366252775

satish

  • Mar 5th, 2012
 

Code
  1. SELECT * FROM emp sample(10);

  Was this answer useful?  Yes

Prabu T

  • Apr 29th, 2012
 

SELECT t.*

FROM < table name > t

minus

SELECT t.*

FROM < table name > t

WHERE t.ord < 91

instead of above query, why don't we use the following query,


SELECT t.*

FROM < table name > t

WHERE t.ord >= 91

Code
  1. SELECT t.*

  2.  

  3. FROM <table name> t

  4.  

  5. WHERE t.ord >= 91

  Was this answer useful?  Yes

Suvarna

  • May 9th, 2012
 

select top 10 * from ;

  Was this answer useful?  Yes

Suvarna

  • May 9th, 2012
 

select * from where rownum <=10;

  Was this answer useful?  Yes

vithal budhe

  • May 29th, 2012
 

SELECT * FROM EMP where rownum<10 ORDER BY HIREDATE DESC;

Code
  1. SELECT * FROM EMP WHERE rownum<10 ORDER BY HIREDATE DESC;

  Was this answer useful?  Yes

Ok, perhaps the message is not coming across clear enough.

The person asking the question is asking for the the "recent 10 records out of 100 records" out of the "100 records" ... "inserted" ... "today" ... "I want first 10 records data from 100 records"

TODAY - RECENT ... these are orders of TIME, the TIME at which the record was inserted into the table. The "recent 10 records"/"first 10 records" are records that have been ordered BY THE TIME INSERTED.

ROWID is location, it is the location of the row on the disk.

ROWID ... DOES NOT EQUAL ... TIME INSERTED
Let me say this again, ROWID ... DOES NOT EQUAL ... TIME INSERTED
And again, ROWID ... DOES NOT EQUAL ... TIME INSERTED

All the previous queries that are using:
... RANK() OVER (ORDER BY ROWID ASC)
... RANK() OVER (ORDER BY ROWID DESC)
... ORDER BY ROWID DESC

Are wrong, they do not answer the question since:

ROWID ... DOES NOT EQUAL ... TIME INSERTED
Let me say this again, ROWID ... DOES NOT EQUAL ... TIME INSERTED
And again, ROWID ... DOES NOT EQUAL ... TIME INSERTED

To get the "recent 10 records"/"first 10 records" insert "today", you need a column or columns that have some order related to the time the record was inserted.

Badal

  • May 31st, 2012
 

It will display top 9 employee details except the 1st one means from the second position it will display

try this code

Code
  1. SELECT * FROM EMP WHERE rownum<10 ORDER BY SAL DESC;

  Was this answer useful?  Yes

Manoj Kumar

  • Aug 28th, 2012
 

Code
  1. SELECT * FROM (SELECT * FROM emp ORDER BY <created_date> DESC) WHERE rownum <11

  Was this answer useful?  Yes

kapil joshi

  • Sep 15th, 2012
 

select * from employees where rownum<10;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions