Forum OpenACS Q&A: is there something like LIMIT for a query in Oracle?

1: is there something like LIMIT for a query in Oracle?

Posted by Rocael Hernández Rizzardini on 02/19/02 03:49 PM

Hi!

I have select and I want it to just give me the first N rows that it 
returns, in PG its simple to do that with LIMIT N, how to do that in 
an oracle query?

e.g. 
PG: select * from users limit 2;
Oracle: select * from users ????

Thanks.

2: Response to is there something like LIMIT for a query in Oracle? (response to 1)

Posted by C. R. Oldham on 02/19/02 03:54 PM

Oracle has a special field called "rownum". So you can say

select user_id from users where rownum < 10;

Unfortunately it does not always behave like you would think. It is calculated on the fly, so

select user_id from users where rownum > 500

won't get you what you want.

3: Response to is there something like LIMIT for a query in Oracle? (response to 1)

Posted by Ben Adida on 02/19/02 04:01 PM

There is a pseudo-field in Oracle called rownum which allows
you to find out what the # of the returned row is. For example:

select * from users where rownum =1;

will do what you're talking about.

Sadly, rownum has this interesting property: it gets set to the # of
the row at the stage of that row being *returned*. Thus, if you
were to do:

select * from users where rownum=2;

guess what? You'd get nothing. Because for there to be a row
with rownum=2, you need to have one with rownum=1 that
comes before it. However, you're preventing that first row from
being returned by having a rownum=2 where clause. Nasty, eh?
You have to use a subquery, something like:

select * from (select *, rownum as inner_rownum from users)
foo where foo.inner_rownum = 2;

4: Response to is there something like LIMIT for a query in Oracle? (response to 1)

Posted by Don Baccus on 02/19/02 04:02 PM

Using rownum effectively is a bit of a pain. Here's a cooked-up example that gets the idea across, even though it may not be exactly right in detail:

select * from
  (select *, rownum as seqnum
   from foo)
where seqnum > 5 and seqnum < 10;

The subselect is necessary because rownum refers to the rows being returned, and something like "rownum > 5" is problematic as the rowset's not built until after query processing involving the where clause.

5: Response to is there something like LIMIT for a query in Oracle? (response to 1)

Posted by Rocael Hernández Rizzardini on 02/19/02 05:18 PM

Thanks for the answers folks!

well, as long as I just wanted the first row that the select returned, I just did

where rownum =1;