InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)
Exchange Links
About this site
How can I limit the number of rows in Oracle
22 comments. Current rating: 
(10 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 2, other pages: [1] 2 | ||
| ||
| ||
| ||
| ||
|
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
| ||
| ||
|
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 !! | ||
| ||
|
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.' | ||
| ||
|
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 | ||
| ||
|
thanks,
good advices | ||
| ||
|
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 2, other pages: [1] 2 |
