You can use a subquery for this like

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

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update:To limit the result with both lower and upper bounds things get a bit more bloated with

select * from ( select a.*, ROWNUM rnum from   ( <your_query_goes_here, with order by> ) a   where ROWNUM <= :MAX_ROW_TO_FETCH )where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2:Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * FROM   sometableORDER BY nameOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Starting from Oracle 12c R1 (12.1), there is a row limiting clause. It does not use familiar LIMIT syntax, but it can do the job better with more options. You can find the full syntax here. (Also read more on how this works internally in Oracle in this answer).

To answer the original question, here's the query:

SELECT * FROM   sometableORDER BY nameOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(For earlier Oracle versions, please refer to other answers in this question)

Examples:

Following examples were quoted from linked page, in the hope of preventing link rot.

Setup

CREATE TABLE rownum_order_test (  val  NUMBER);INSERT ALL  INTO rownum_order_testSELECT levelFROM   dualCONNECT BY level <= 10;COMMIT;

What's in the table?

SELECT valFROM   rownum_order_testORDER BY val;       VAL----------         1         1         2         2         3         3         4         4         5         5         6         6         7         7         8         8         9         9        10        1020 rows selected.

Get first N rows

SELECT valFROM   rownum_order_testORDER BY val DESCFETCH FIRST 5 ROWS ONLY;       VAL----------        10        10         9         9         85 rows selected.

Get first N rows, if N^th row has ties, get all the tied rows

SELECT valFROM   rownum_order_testORDER BY val DESCFETCH FIRST 5 ROWS WITH TIES;       VAL----------        10        10         9         9         8         86 rows selected.

Top x% of rows

SELECT valFROM   rownum_order_testORDER BY valFETCH FIRST 20 PERCENT ROWS ONLY;       VAL----------         1         1         2         24 rows selected.

Using an offset, very useful for pagination

SELECT valFROM   rownum_order_testORDER BY valOFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;       VAL----------         3         3         4         44 rows selected.

You can combine offset with percentages

SELECT valFROM   rownum_order_testORDER BY valOFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;       VAL----------         3         3         4         44 rows selected.

I did some performance testing for the following approaches:

Asktom

select * from (  select a.*, ROWNUM rnum from (    <select statement with order by clause>  ) a where rownum <= MAX_ROW) where rnum >= MIN_ROW

Analytical

select * from (  <select statement with order by clause>) where myrow between MIN_ROW and MAX_ROW

Short Alternative

select * from (  select statement, rownum as RN with order by clause) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Results

Table had 10 million records, sort was on an unindexed datetime row:

• Explain plan showed same value for all three selects (323168)
• But the winner is AskTom (with analytic following close behind)

Selecting first 10 rows took:

• AskTom: 28-30 seconds
• Analytical: 33-37 seconds
• Short alternative: 110-140 seconds

Selecting rows between 100,000 and 100,010:

• AskTom: 60 seconds
• Analytical: 100 seconds

Selecting rows between 9,000,000 and 9,000,010:

• AskTom: 130 seconds
• Analytical: 150 seconds