JayaRam Kandimalla

 Profile Answers by JayaRam Kandimalla 

• Dec 3rd, 2011

 

Use this

Code
WHERE rownum < 11

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Dec 5th, 2011

 

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
SELECT t.*


FROM <table name> t


WHERE t.ord < 11


 


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


 


SELECT t.*


FROM <table name> t


minus


SELECT t.*


FROM <table name> t


WHERE t.ord < 91

would give the 10 most recently inserted records.

Susil Kumar Nagarajan

• Jan 21st, 2012

 

Analytic function will serve the purpose. See below SQL.

Code
SELECT *


FROM


  (SELECT *


  FROM


    (SELECT rowid,


      e.*,


      rank() over (ORDER BY rowid ASC) rowid_rank


    FROM employee e


    )


  WHERE rowid_rank >


    (SELECT COUNT(1)-100 FROM employee


    )


  ORDER BY 1 ASC


  )


WHERE ROWNUM < 11;


 

sajal

• Feb 9th, 2012

 

Code
SELECT * FROM 


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


         WHERE rank<=10 ORDER BY rowid

Balapradee Reddy

• Feb 14th, 2012

 

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

Mohammad Sarwar

• Feb 17th, 2012

 

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

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Mar 1st, 2012

 

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
SELECT * FROM emp sample(10);

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
SELECT t.*


 


FROM <table name> t


 


WHERE t.ord >= 91

deepika verma

• May 1st, 2012

 

Code
SELECT *


  FROM (SELECT   ROWNUM, a.*


            FROM < TABLE name > a


        ORDER BY ROWID DESC)


 WHERE ROWNUM BETWEEN 1 AND 100

Suvarna

• May 9th, 2012

 

select top 10 * from ;

Suvarna

• May 9th, 2012

 

select * from where rownum <=10;

Gunjan David

 Profile Answers by Gunjan David Questions by Gunjan David

• May 13th, 2012

 

@deepika you CANt use rownum keyword in BETWEEN OPERTAOR.......

ptmich

 Profile Answers by ptmich 

• May 27th, 2012

 

Code
SELECT top 10 * FROM TABLE


ORDER BY rowid DESC;


 

vithal budhe

• May 29th, 2012

 

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

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

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• May 29th, 2012

 

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
SELECT * FROM EMP WHERE rownum<10 ORDER BY SAL DESC;

Manoj Kumar

• Aug 28th, 2012

 

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

kapil joshi

• Sep 15th, 2012

 

select * from employees where rownum<10;