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 sometable ORDER BY name OFFSET 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_test SELECT level FROM dual CONNECT BY level <= 10; COMMIT; 

What's in the table?

SELECT val FROM rownum_order_test ORDER BY val; VAL ---------- 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 20 rows selected. 

Get first N rows

SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY; VAL ---------- 10 10 9 9 8 5 rows selected. 

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

SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS WITH TIES; VAL ---------- 10 10 9 9 8 8 6 rows selected. 

Top x% of rows

SELECT val FROM rownum_order_test ORDER BY val FETCH FIRST 20 PERCENT ROWS ONLY; VAL ---------- 1 1 2 2 4 rows selected. 

Using an offset, very useful for pagination

SELECT val FROM rownum_order_test ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY; VAL ---------- 3 3 4 4 4 rows selected. 

You can combine offset with percentages

SELECT val FROM rownum_order_test ORDER BY val OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY; VAL ---------- 3 3 4 4 4 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

SQL Standard

Since version 12c Oracle supports the SQL:2008 Standard, which provides the following syntax to limit the SQL result set:

SELECT title FROM post ORDER BY id DESC FETCH FIRST 50 ROWS ONLY 

Oracle 11g and older versions

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

SELECT * FROM ( SELECT title FROM post ORDER BY id DESC ) WHERE ROWNUM <= 50 

An analytic solution with only one nested query:

SELECT * FROM ( SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t ) WHERE MyRow BETWEEN 10 AND 20; 

Rank() could be substituted for Row_Number() but might return more records than you are expecting if there are duplicate values for name.

On Oracle 12c (see row limiting clause in SQL reference):

SELECT * FROM sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; 

Pagination queries with ordering are really tricky in Oracle.

Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views.

ROWNUM is a pseudocolumn that gets many people into trouble. A ROWNUM value is not permanently assigned to a row (this is a common misunderstanding). It may be confusing when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting.

What is more, a ROWNUM value is incremented only after it is assigned.

This is why the followin query returns no rows:

 select * from (select * from some_table order by some_column) where ROWNUM <= 4 and ROWNUM > 1; 

The first row of the query result does not pass ROWNUM > 1 predicate, so ROWNUM does not increment to 2. For this reason, no ROWNUM value gets greater than 1, consequently, the query returns no rows.

Correctly defined query should look like this:

select * from (select *, ROWNUM rnum from (select * from skijump_results order by points) where ROWNUM <= 4) where rnum > 1; 

Find out more about pagination queries in my articles on Vertabelo blog:

• Oracle ROWNUM Explained
• Top-N and pagination queries

As an extension of accepted answer Oracle internally uses ROW_NUMBER/RANK functions. OFFSET FETCH syntax is a syntax sugar.

It could be observed by using DBMS_UTILITY.EXPAND_SQL_TEXT procedure:

Preparing sample:

CREATE TABLE rownum_order_test ( val NUMBER ); INSERT ALL INTO rownum_order_test SELECT level FROM dual CONNECT BY level <= 10; COMMIT; 

Query:

SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY; 

is regular:

SELECT "A1"."VAL" "VAL" FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0", ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" FROM "ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC; 

db<>fiddle demo

Fetching expanded SQL text:

declare x VARCHAR2(1000); begin dbms_utility.expand_sql_text( input_sql_text => ' SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY', output_sql_text => x); dbms_output.put_line(x); end; / 

WITH TIES is expanded as RANK:

declare x VARCHAR2(1000); begin dbms_utility.expand_sql_text( input_sql_text => ' SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS WITH TIES', output_sql_text => x); dbms_output.put_line(x); end; / SELECT "A1"."VAL" "VAL" FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0", RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" FROM "ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC 

and offset:

declare x VARCHAR2(1000); begin dbms_utility.expand_sql_text( input_sql_text => ' SELECT val FROM rownum_order_test ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY', output_sql_text => x); dbms_output.put_line(x); end; / SELECT "A1"."VAL" "VAL" FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0", ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" FROM "ROWNUM_ORDER_TEST" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=CASE WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 ORDER BY "A1"."rowlimit_$_0" 

With 21c version, you can simple apply a limit as follows:

select * from course where ROWNUM <=10; 

Less SELECT statements. Also, less performance consuming. Credits to: [email protected]

SELECT * FROM (SELECT t.*, rownum AS rn FROM shhospede t) a WHERE a.rn >= in_first AND a.rn <= in_first; 

I'v started preparing for Oracle 1z0-047 exam, validated against 12c While prepping for it i came across a 12c enhancement known as 'FETCH FIRST' It enables you to fetch rows /limit rows as per your convenience. Several options are available with it

- FETCH FIRST n ROWS ONLY - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows - n % rows via FETCH FIRST N PERCENT ROWS ONLY 

Example:

Select * from XYZ a order by a.pqr FETCH FIRST 10 ROWS ONLY 

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

 SELECT * FROM sometable1 so WHERE so.id IN ( SELECT so2.id from sometable2 so2 WHERE ROWNUM <=5 ) AND ORDER BY so.somefield AND ROWNUM <= 100 

I have implemented this in oracle server 11.2.0.1.0

you can also use following query

SELECT "ID", "COL1", "ETC.." FROM tblTest WHERE "COL1" = "test" ORDER BY "ID" DESC OFFSET x ROWS FETCH NEXT y ROWS ONLY; 

in this case you can skip first x rows and limit resault into next y rows. then you can play around with x and y inorder to paginate over your data.

If the orderby column used in pagination has duplicate values. We can first order by to generate a particular ordering and then limit the case

 SELECT T.* FROM ( SELECT * FROM Table ORDER BY "name" ) T OFFSET (1)*100 ROWS FETCH NEXT 100 ROWS ONLY 

select * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, FROM EMP ) EMP where ROWID=5 

greater then values find out

select * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, FROM EMP ) EMP where ROWID>5 

less then values find out

select * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, FROM EMP ) EMP where ROWID=5