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?

In MySQL, I can do this:

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

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

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

Foreglimpse

Standardized in SQL:2008. – Tantara

Looks like the offset and row_count are reversed in your first MySQL example. That query will select rows 11 to 30, not 21 to 30. – Imco

Limit was announced by Tom Kyte for Oracle 12c... – Ligroin

Consider it offtopic but I can hardly imagine real life use-cases when you need to extract data from the middle of dataset. No kidding. Maybe I am just a lucky one that haven't come accross such requirements yet. If someone could give an example especially based on real experience... – Paperback

Fetching the next page in a result set? – Foreglimpse

@YaroslavShabalin In particular, a paged search uses this pattern all the time. Almost any app with any kind of search function is going to use it. Another use case would be loading only part of a long list or table client side and giving the user the option to expand. – Halfcocked

@MathieuLongtin @Halfcocked In google do you initially begin from page number 10? Why need to execute query each time the next page is loaded when you can execute it once, open cursor and navigate by loading certain part of dataset? Yes you have to deal with memory management things if resultset is large, but avoid possible inconsistency. Consider that number of rows in resultset changes, each time you re-query with above limit 20,10 you can get different result. – Paperback

@YaroslavShabalin You can't get a different result set unless the underlying data changes because of the ORDER BY. That's the whole point of ordering first. If the underlying data changes and your result set changes because of it, then why not show the user the updated results instead of outdated information? Also, state management is a plague to be avoided as much as possible. It's a constant source of complication and bugs; that's why functional is getting so popular. And when would you know to expire the entire result set in memory? In web, you have no way of knowing when the user leaves. – Halfcocked

@Halfcocked OK, I get your point. Now it makes sense. – Paperback

@YaroslavShabalin - opening a cursor and holding it open for an extended period of time in a multi-user scenario is a good way to tie a relational database in knots. In a single-user database this probably doesn't matter, but when you've got hundreds of simultaneous connections this is very likely to lead to problems. Relational databases are generally designed as transactional DB's with relatively short transaction durations. YMMV. Share and enjoy. – Regeneracy

See How pagination works in Oracle SQL – Amain

See also Paging with Oracle – Spadix

youtube.com/watch?v=rhOVF82KY7E – Donata

Answer 1

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.

-- only get first 10 results
SELECT * 
FROM   sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- get result rows 20-30
SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

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

Winchester

This is definitely the way to do it, but be aware (as the ask tom article says) the query performance degrades as your max rownum increases. This is a good solution for query results where you only want to see the first few pages, but if you are using this as a mechanism for code to page through a whole table you would be better off refactoring your code – Vancouver

+1 your lower/upper version actually helped me work around an issue where a mere upper-bounded rownum clause was drastically slowing down my query. – Fearfully

The Leigh Riffel "analytic solution with only one nested query" is the one. – Ferrand

The AskTom article has an optimizer hint as well that uses SELECT /*+ FIRST_ROWS(n) / a., rownum rnum The closing slash should be preceded by an asterisk. SO is scrubbing it out. – Thrive

Note that for Oracle 11 an outer SELECT with ROWNUM will prevent you from calling deleteRow on an UpdatableResultSet (with ORA-01446) - looking forward to that 12c R1 change! – Santasantacruz

In the first query, doesn't the inner query have to retrieve all rows before the external one can filter out all but the first five? – Fining

Unlike the FETCH NEXT n ROWS approach, the subquery with ROWNUM restriction is applicable together with the "FOR UPDATE" clause. – Peanuts

The first query is also valuable because original set of columns is preserved. In environments where query result is subsequently processed by some ORM tool, it's necessary to get n columns from table t.* rather than n+1 columns including some kind of renamed rownum. Nonstandard select * except rnum clause would solve this problem, however very few databases support it at this moment AFAIK. Also, the rownum-based solution easily works only to emulate limit clause. Emulating offset clause is harder because of timing of computing rownum (can be circumvented using reversed order). – Gumm

Answer 2

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.

Lin

We need tool support for keyset pagination – Barefaced

Just to extend: OFFSET FETCH syntax is a syntax sugar. Details – Barefaced

How can we get the LIMIT and OFFSET in Oracle 11G ? – Vagabondage

@Pra_A There's no native support in 11G for LIMIT/OFFSET. If you check the other answers they all have in one way or other actually implemented the limit and offset. – Lin

Answer 3

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

Housefather

Nice work. Did you try the short alternative with a between instead of >= and <=? – Foreglimpse

@MathieuLongtin BETWEEN is just a shorthand for >= AND <= (https://mcmap.net/q/46408/-between-clause-versus-lt-and-gt) – Phototransistor

zeldi - Which version was this on? Oracle has made analytic performance improvements in 11.1. and 11.2. – Sabinasabine

@Leigh Riffel It was 10.2.0.5; one day I might take time and also check the 11i version. – Housefather

I ran some quick tests and got similar results for 12c. The new offset syntax has the same plan and performance as the analytic approach. – Plasma

myrow is undefined – Banger

As a newcomer to Oracle in a place with 10c I find it baffling this was not implemented natively like in other sql – Burnie

Answer 4

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.

Sabinasabine

I love analytics. You might want to clarify what the difference in behavior would be between Rank() and Row_Number(). – Antonio

Indeed, not sure why I didn't think about duplicates. So, in this case if there are duplicate values for name then RANK could give more records than you are expecting therefore you should use Row_Number. – Sabinasabine

If mentioning rank() it is also worth noting dense_rank() which may be more useful for output control as the latter does not "skip" numbers, whereas rank() can. In any case for this question row_number() is best suited. One other not is this technique is applicable to any db that supports the functions mentioned. – Tactful

Answer 5

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

Haleyhalf

I am curious as to, there wasnt ever a syntax in Oracle that supported the usage of "select TOP N * from {TableName}" or something like that? – Yeomanly

@Yeomanly Nope. That's just SQL Server. – Haleyhalf

Answer 6

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

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

Heavensent

And of course, they had to use a totally different syntax than everybody else so far – Foreglimpse

Clearly after sitting down with all the other vendors to agree on LIMIT in SQL:2008 they then had to take a leaf out of Microsoft's book and break the standard. – Heavensent

Interestingly I heard recently that the most recent standard includes this syntax, so maybe Oracle pushed it in first before implementing. Arguably it is more flexible than LIMIT ... OFFSET – Heavensent

@Derek: Yes, not following the standard is regrettable. But newly introduced functionality in 12cR1 is more powerful than just LIMIT n, m (See my answer). Then again, Oracle should have implemented LIMIT n, m as syntactic sugar, as it is equivalent to OFFSET n ROWS FETCH NEXT m ROWS ONLY. – Lin

@Derek: Actually, I just noticed this remark in the PostgreSQL manual postgresql.org/docs/9.0/static/sql-select.html#AEN69535 "The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used by MySQL. The SQL:2008 standard has introduced the clauses OFFSET ... FETCH {FIRST|NEXT} ... for the same functionality". So LIMIT was never part of the standard. – Heavensent

@Heavensent I found your link broken (because of changed anchor?), proper is postgresql.org/docs/9.0/static/sql-select.html#AEN69605 (for now of course ;-)) – Firebrat

@Wirone: For now: postgresql.org/docs/9.0/static/sql-select.html#SQL-LIMIT – Lin

@Krumia Thanks. Could even use www.postgresql.org/docs/current/static/sql-select.html#SQL-LIMIT for most recent version – Heavensent

All standardization argument aside, if you just need something that works for Oracle, as of 2014 this is now the best of all the answers, IMHO. +1 – Dannie

Answer 7

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

Splashboard

The first row of the query result does not pass ROWNUM > 1 predicate (…) – upvote for explaining this. – Facelift

Incredible! To this day, oracle doesn't have a performative solution for this. Between, for example, it's VERY slow! – Paris

Answer 8

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"

Barefaced

Answer 9

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

select * from course where ROWNUM <=10;

Artemisia

Read the second part of my question. This doesn't work, and it's been around much longer than 21c – Foreglimpse

Answer 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;

Youngs

Furthermore, it is totally incorrect answer. Question was about limiting AFTER the sorting. So rownum should be out of subquery. – Rivalry

Answer 11

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

Habitue

https://mcmap.net/q/45364/-how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering - this has already been provided in other answers. Please refrain from posting stuff that's already been posted months ago. – Dionysian

oh sure,didn't go through every answer, i came across the subquery ones early on,will keep that in mind. – Habitue

Answer 12

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

Eatables

downvote as the question asks about limiting ordered rows and you don't even have order – Facelift

@PiotrDobrogost Understand that is not a huge task, ordering keywords are common for all rdbms only limit has changes. – Eatables

Answer 13

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.

Conceptualize

Answer 14

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

Fortalice

Downvote as ROW_NUMBER() based solution had already been posted by Leigh Riffel. In addiction there are syntax errors in code shown. – Facelift

Answer 15

(untested) something like this may do the job

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

There is also the analytic function rank, that you can use to order by.

Oldham

This won't return a single row as the ROWNUM is a column on the resultset so that last WHERE condition will always be false. Plus you can't use ROWNUM and an ORDER BY an guarantee ORDER. – Brahmin

Excellent. Let's leave this here as a warning to others. – Oldham