How do I limit the number of rows returned by an Oracle query after ordering

Question

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

This is how I did this In MySQL, :

select * 
from sometable
order by name
limit 20,10

to reach rows 21 through 30 (skip the first 20, give the next 10). Since the rows are chosen after the order by, it actually begins with the 20th name in alphabetical order.

The rownum pseudo-column in Oracle is all that is mentioned, but it is evaluated before order by, which means that:

select * 
from sometable
where rownum <= 10
order by name

Will give me ten rows arranged in a random order, which is not what I want most of the time. In addition, no offset can be specified. 

Can someone please help me with this?

Answer 1

Use a subquery for this like:

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

I hope this helps you.