Limit the number of rows returned by an Oracle query

1129455 pts.
Tags:
MySQL
Oracle
Oracle Query
Would there be a way to make an Oracle SQL query behave like it contains a MySQL limit clause? So, for example, here's what I can do in MySQL:
select * 
from sometable
order by name
limit 20,10
This allows me to get the 21st to the 30th rows (meaning skip the first 20, give the next 10). The rows are already selected after the "ORDER BY" so it starts on the 20th name alphabetically. But in Oracle, the only thing that comes close is the "ROWNUM" pseudo-column, which is evaulated before "ORDER BY", which gives us this:
select * 
from sometable
where rownum <= 10
order by name
That gives us a random set of rows ordered by name. That's what I don't want. Any help here?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this: (from asktom article)
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

select * from
( select a.*, ROWNUM rnum from
  ( select * from employee order by employee_last_name ) a
  where ROWNUM <= 30 )
where rnum  >= 21

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: