By Mathieu Longtin


2009-01-22 19:48:43 8 Comments

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.

14 comments

@Sumesh TG 2018-10-15 09:13:25

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

@Piotr Dobrogost 2019-03-13 08:23:20

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

@Sumesh TG 2019-03-13 08:48:40

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

@zeldi 2011-06-30 14:20:24

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

@Mathieu Longtin 2011-07-05 14:55:44

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

@wweicker 2011-10-20 15:27:25

@MathieuLongtin BETWEEN is just a shorthand for >= AND <= (stackoverflow.com/questions/4809083/between-clause-versus-a‌​nd)

@Leigh Riffel 2012-09-26 12:03:28

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

@zeldi 2013-03-11 09:17:05

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

@Jon Heller 2014-01-18 04:17:22

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.

@shinzou 2018-04-04 10:50:33

myrow is undefined

@Mehul Akabari 2017-04-12 11:01:46

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

@Piotr Dobrogost 2019-03-13 08:33:12

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

@sampathsris 2014-09-26 04:01:24

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.

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 Nth 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.

@Lukasz Szozda 2017-10-26 04:32:47

@sampathsris 2017-10-26 08:31:14

@lad2025: Interesting read. Hmmm...

@sandi 2016-12-08 10:11:23

If you are not on Oracle 12C, you can use TOP N query like below.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

You can even move this from clause in with clause as follows

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

Here actually we are creating a inline view and renaming rownum as rnum. You can use rnum in main query as filter criteria.

@uom-pgregorio 2018-05-10 20:28:56

In my case this didn't return the right rows. What I did to fix it is to do the ORDER BY and the rownum separately. Basically I created a sub-query that had the ORDER BY clause.

@Piotr Dobrogost 2019-03-13 09:06:52

Downvote as it is incorrect answer. Question was about limiting after the sorting so rownum should be outside of a subquery.

@arjun gaur 2016-06-01 10:31:02

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

@Mat 2016-06-01 11:43:41

stackoverflow.com/a/26051830/635608 - this has already been provided in other answers. Please refrain from posting stuff that's already been posted months ago.

@arjun gaur 2016-06-01 12:48:47

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

@Piotr Dobrogost 2019-03-13 08:19:58

downvote as a duplicate of other answers

@Leigh Riffel 2009-01-23 14:28:08

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.

@Dave Costa 2009-01-23 16:53:57

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

@Leigh Riffel 2009-01-26 14:11:03

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.

@Used_By_Already 2017-10-29 00:34:39

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.

@beldaz 2013-09-24 03:09:56

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

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

@Mathieu Longtin 2013-09-25 01:12:42

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

@beldaz 2013-09-25 01:39:16

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.

@Derek 2013-12-19 10:19:56

Its insane. Why not stick to the standard!

@beldaz 2013-12-31 23:08:04

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

@sampathsris 2014-09-26 13:54:21

@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.

@beldaz 2015-05-21 00:58:42

@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.

@Wirone 2015-10-23 11:51:40

@beldaz 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 ;-))

@sampathsris 2016-03-08 04:06:04

@beldaz 2016-03-08 04:13:48

@Krumia Thanks. Could even use www.postgresql.org/docs/current/static/sql-select.html#SQL-L‌​IMIT for most recent version

@trpt4him 2016-05-16 13:30:04

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

@Kosi2801 2009-01-22 19:55:58

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   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

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

@Chris Gill 2009-08-27 12:30:53

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

@Kelvin 2011-08-09 22:21:18

+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.

@Darren Hicks 2012-03-27 23:22:07

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

@David Mann 2013-03-05 15:34:10

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.

@nsandersen 2015-05-11 08:34:27

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!

@Tulains Córdova 2015-12-02 12:48:34

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?

@Rakesh 2014-08-20 18:33:14

In oracle

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

VAL

    10
    10
     9
     9
     8

5 rows selected.

SQL>

@Mat 2014-08-21 09:55:49

You should specify that this applies starting from Oracle 12c, and that you copy/pasted that from somewhere - please always cite your sources.

@sampathsris 2014-09-26 14:03:34

The source is this @Mat. And Rakesh, please try at least to adapt the answer for the original question. I have also provided an answer quoting the same source, but I tried to be comprehensive and cited the original source.

@Bartek 2014-04-12 17:32:50

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:

@Piotr Dobrogost 2019-03-13 09:11:12

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

@Felipe Q. Giovanoni 2011-03-02 14:32:32

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;

@Mathieu Longtin 2011-03-07 15:55:09

I don't see how this is better than the accepted answer.

@BitLord 2017-09-18 07:20:24

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

@stimepy 2010-11-18 17:17:03

Same as above with corrections. Works but definitely not pretty.

   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

Honestly, better to use the above answers.

@Ben 2013-09-08 12:21:30

This is incorrect as the WHERE clause is evaluated before the ORDER BY.

@EvilTeach 2015-09-15 12:30:32

Interestingly stolen from my bad answer below.

@EvilTeach 2009-01-22 20:21:56

(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.

@Ben 2013-09-08 12:20:36

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.

@EvilTeach 2014-01-21 15:33:25

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

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

8 Answered Questions

[SOLVED] MYSQL - ORDER BY & LIMIT

33 Answered Questions

[SOLVED] How to query MongoDB with "like"?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

2 Answered Questions

1 Answered Questions

[SOLVED] Why can Oracle not select truthiness of a statement in the order by cluase

1 Answered Questions

1 Answered Questions

[SOLVED] Using rownum in oracle SQL statement in combination with order by clause

  • 2012-05-03 11:49:19
  • nabulke
  • 14924 View
  • 4 Score
  • 1 Answer
  • Tags:   sql oracle

1 Answered Questions

[SOLVED] LIMIT / OFFSET in Oracle 11G

Sponsored Content