Oracle LIMIT clause?

Anon

In PostgreSQL, I can execute:

select * from mytable limit 5 offset 10;

I believe the equivalent in MySQL is:

select * from mytable limit 10,5

In whatever order the database returns rows, it only returns, of the entire set, the 5 rows start at row 10.

What is the equivalent of this in Oracle?

brandonschnell

just a wild guess:

select * from mytable where rownum >= 10 and rownum <= 15;

steved

That select statement will get you nothing.

The rownum starts at zero and isn't incremented until you have something that matches the where clause. Zero is never > ten, so you get nothing. You can only (successfully) use rownum in a <= comparison.

Anon

My extremely ugly hack is:

SELECT <enumerate mytable columns> FROM (SELECT <enumerate mytable columns>, ROWNUM FROM (SELECT * FROM mytable ORDER BY x)) WHERE rownum > 24 AND rownum <= 24+12;

This is the most disguisting SQL I've ever seen. Is there NO way to get anything better?

kparker

Is there NO way to get anything better?

Anybody know Larry Ellison's direct number? :-(

Anon

This query will work:

Select
from mytable
where rownum<=15
minus
select
from mytable
where rownum<=10

That will get you rows 11-15.

Anon

Gerald,

Try this:

SELECT * FROM mytable
WHERE ROWNUM >= 10
AND ROWNUM <= 15;

-Eric

jamesbond0065

SELECT field1, field2 FROM (SELECT ROWNUM rnum, field1, field2 FROM sometable) WHERE rnum BETWEEN 30 AND 40 AND rownum < 11;

Anon

Quick question on this ... why the indented SELECT statement? What's the difference between rnum and rownum?

I've checked this forum extensively - is there no single answer to this burning question?

jamesbond0065

There is no need for indented SELECT. When I typed it accidentally came out like that.

Another version of my previous example is:

SELECT field1, field2 FROM (SELECT ROWNUM rnum, field1, field2 FROM sometable where whatever condition you want to use here) WHERE rnum BETWEEN 30 AND 39;

rnum is the row number of the first matched records (statement in paranthesis got processed first); 30 and 39 are just the low and high record numbers you want to retrieve.

What you do here is you select from the result of a previous select. In other words, say the first select gives you 1204 matched records, the second select retrieves from 1 to 25, or from 25 to 40, or whatever range you want to retrieve within the 1204 matched records.

Good luck.

Anon

Hi James,

Thanks so much for your help...

What's the difference between what you mentioned above and the following statement:

SELECT ROWNUM rnum, field1, field2 FROM sometable where whatever condition you want to use here AND rnum BETWEEN 30 AND 39;

Is this type of indented SELECT the most memory efficient solution Oracle has?
Why do you need to select the fields more than once in the statement above, ie:

SELECT field1, field2 FROM (SELECT ROWNUM rnum, field1, field2 ... ) WHERE rnum BETWEEN 30 AND 39;

instead of

SELECT field1, field2 FROM (SELECT ROWNUM rnum WHERE Conditional) WHERE rnum BETWEEN 30 AND 39;

Many thanks for your help -- it's much appreciated!

-- John G.

Anon

Hey,

Would this statement work?

SELECT JOE AS 'Joe', SOAP AS 'Soap', TEST AS 'Test', PRODUCT AS 'Product', ROWNUM AS 'RowNum' FROM Selections WHERE RowNum < 50 AND Joe = 'Soap' AND Product = 'Omo' ORDER BY Mojo MINUS (SELECT JOE AS 'Joe', SOAP AS 'Soap', TEST AS 'Test', PRODUCT AS 'Product', ROWNUM AS 'RowNum' FROM Selections WHERE RowNum < 5 AND Joe = 'Soap' AND Product = 'Omo' ORDER BY Mojo)

Thanks

-- J

aeon_

This one worked for me (I wanted the limit/offset and all rows sorted):

SELECT FROM
(SELECT ROWNUM rnum,[all needed fields]
FROM (SELECT FROM tablename ORDER BY sortfield)
WHERE ROWNUM <= 40) WHERE rnum > 20;

Maybe there will be an easier way with the next Oracle version.

AEon

Anon

This is actually the best statement here to achieve the wanted result.
Unfortunately the "ORDER BY" in the subselect works only for Oracle 8i and newer.

The solution that worked for me was to create a view and the simply select my wanted results with the condition: where rownum between ...

Anon

if you want to avoid to know previusly the colum names you can try this

SELECT from(select ROWNUM as rn, a. FROM (SELECT * FROM table where anything ) a)WHERE rn >= 10 AND rn <= 20

curiosly this doesn´t work
select ROWNUM , a. FROM (SELECT FROM table where anything ) a WHERE ROWNUM >= 10 AND ROWNUM <= 20

Anon

hmm but ORDER BY Clause is still
not working. I mean
the rownum is still being done before
the records has been sorted.

example:
SELECT
field1, field2
FROM
(SELECT
ROWNUM rnum, field1, field2
FROM
<tablename>
ORDER BY
field2 DESC
)
WHERE
rnum BETWEEN 0 AND 50

How can i retrieve a top ten list of a huge
table (2 million records) without passing the whole resultset to the application?

Anon

limiting a ordered view result to first 3 rows:

select from (select from v_listing_full where sid = 39 and utc >= '00011216232500' order by utc) where rownum<4

Anon

This Oracle SQL will do the same as the MySQL limit syntax:

select ROWNUM, MYFIELD from
(
select rownum seq_num,
MYFIELD
from (
select MYFIELD
from MYTABLE
where MYFIELD like 'B%'
order by MYFIELD DESC
)
)
where seq_num between 0 and 6