Databases InterBase (28) MS-SQL (5) mysql (32) Oracle (1)
Exchange Links About this site Links to us
|
How can I limit the number of rows in Oracle
16 comments. Current rating: (7 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.
 | |  | |
select col from tbl limit 20;
select col from tbl where rownum<=20;
select top 20 col from tbl; | |  | |  |
Comments:
| You are on page 1 of 2, other pages: [1] 2 | |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This is also equivalent to the following MS Sql Server statement:
select top 20 * from tbl
I've been pulling my hair out trying to figure out how to do this in Oracle!
|
|
|
|
|
Why not wrap this query in a sub-select and use the rownum = n to limit your resultSet to size n. Hope this helps.
|
|
|
|
|
Rownum isn't exactly the right solution.
i mean if i want to fetch from the register # 20 TO # 40
i cannot use SELECT field1,field2,field3 FROM mytable WHERE ROWNUM >= 20 AND ROWNUM <=40
so.. i did a 'trick' to do that..
i did:
SELECT * FROM (
SELECT rowid as ID,field1,field2,field3 FROM mytable
)
WHERE id BETWEEN 20 AND 40
i hope, this work for you ;)
|
|
|
|
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) |
|
|
|
This thing worked for me
SELECT * FROM
(SELECT field, ROW_NUMBER() OVER (ORDER BY field) R FROM table)
WHERE R BETWEEN 0 and 100
|
|
|
|
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.'
|
2007-10-03, 06:20:36 (updated: 2007-10-03, 06:24:39) |
|
|
|
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
|
|
|
|
|
how to paging in php with database oracle?
and the same function with mysql_num_rows in oracle?
and how to calculet the rows???
|
|
|
|
|
Works perfectly, thanks!
|
|
|
|
|
select *from (select *from tab1 where rowid<=20);
why because rowid 's are always different and perninent so we can use the rowid and find the first twenty records
|
| You are on page 1 of 2, other pages: [1] 2 |
|