DelphiFAQ Home Search:

How can I limit the number of rows in Oracle

 

comments32 comments. Current rating: 5 stars (15 votes). Leave comments and/ or rate it.

Question:

I have got a query that returns thousands of rows but I'm only interested in the first 20 records. In mysql I can limit the returned data (and thus the network traffic) with the LIMIT start,number clause, where start is the starting row and number is the number of rows that I want to see.

Is something similar possible in Oracle as well?

Answer:

Oracle has a system attribute ROWNUM for each record returned. A query that only returns the first 20 records would look like the one in the example.

ROWNUM starts counting with 1.

// mysql
select col from tbl limit 20;

// Oracle
select col from tbl where rownum<=20;

// Microsoft SQL
select top 20 col from tbl;

Comments:

You are on page 1 of 3, other pages: [1] 2 3
2006-05-23, 10:25:30
anonymous from Italy  
rating
2006-07-28, 14:32:08
anonymous from United States  
rating
2006-10-10, 14:47:55
anonymous from United States  
rating
2007-03-26, 21:37:29
anonymous from United States  
Actually, ROWNUM is not exactly right either. It may work here and this time, but if you ever decide to order the resultset, it will break.

See:
http://www.oracle.c..ktom.html
2007-04-29, 06:50:30   (updated: 2007-04-29, 06:52:19)
ali from Pakistan  
This thing worked for me

SELECT * FROM
(SELECT field, ROW_NUMBER() OVER (ORDER BY field) R FROM table)
WHERE R BETWEEN 0 and 100
2007-05-06, 20:11:07
marcelo,from,chile from Chile  
This thing worked for me
SELECT * FROM
(SELECT field, ROW_NUMBER() OVER (ORDER BY field) R FROM table)
WHERE R BETWEEN 0 and 100

that one did the trick !!
2007-09-18, 01:09:27
Anil,TANGUL from Istanbul, Turkey  
rating
http://www.oracle.c..ktom.html

select *
from
( select rownum rnum, a.*
from (your_query) a
where rownum <= :M )
where rnum >= :N;

in order to get rows n through m from 'your query.'
2007-10-03, 06:20:36   (updated: 2007-10-03, 06:24:39)
anonymous from Austria  
The SQL code posted by my friend from Venezuela is simple and fast, except the code provides an error in 10g because of a little mistake:
instead of 'rowid' it has to be 'rownum'

SQL Revised:
SELECT * FROM (
SELECT ROWNUM as ID, field1, field2, field3 FROM mytable
)
WHERE id BETWEEN 20 AND 40;
1ms;

Have a nice time everybody! ;)

Thanks

Ash
2007-10-31, 08:43:21
anonymous from Turkey  
thanks,
good advices
2008-08-08, 12:43:11
anonymous from Brazil  
rating
This thing worked for me
SELECT * FROM
(SELECT field, ROW_NUMBER() OVER (ORDER BY field) R FROM table)
WHERE R BETWEEN 0 and 100
You are on page 1 of 3, other pages: [1] 2 3

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: