Oracle "LIMIT n,m" equivalent

Question

Possible Duplicate:
How do I limit the number of rows returned by an oracle query?

I have this query

select * from pr_device_reading order by date_reading DESC

Note that this query is normalized, constructed by a method, and the table name will change according to the method's argument, so I can't specify the column names (and I do want all the columns in the result anyway).

What is the equivalent of

SELECT * 
 FROM pr_lecture_combustibles 
ORDER BY date_lecture DESC 
LIMIT 10,20;

using Oracle SQL?

I have tried

SELECT * 
  FROM (SELECT * 
          FROM pr_lecture_combustibles 
         ORDER BY date_lecture DESC) 
 WHERE ROWNUM BETWEEN 10 AND 20;

and other syntax, but I get no result, "missing expression" messages and other errors.

Thanks

I believe you need to give an alias to the derived table. `FROM (....) AS A`. — Lamak, Apr 20 '11 at 19:31
In Oracle 12c use FETCH: http://stackoverflow.com/a/18972336/290182 — beldaz, Sep 24 '13 at 03:13
It is a valid search. this is about alternate for limit clause which does much more than limiting rows. — Jafar Ali, Jul 23 '14 at 07:04
This question's title is far more generic than the marked duplicate. I'd even say the title is _beautiful_. +1ed. @YanickRochon perhaps we can make this question a community wiki, and make the body more generic, thereby making this a canonical question? — sampathsris, Sep 26 '14 at 04:13
An answer that works on Oracle 12c R1 and later: http://stackoverflow.com/a/26051830/1461424 — sampathsris, Sep 26 '14 at 04:16

score 2 · Accepted Answer · answered Apr 20 '11 at 19:31

If you can accept getting an additional column for the row number, you can apply the ROW_NUMBER analytic function and do something like this

SELECT *
  FROM (SELECT a.*,
               ROW_NUMBER() OVER( order by date_lecture desc ) rn
          FROM pr_lecture_combustibles a)
 WHERE rn BETWEEN 10 AND 20

But you'll get all the columns in the table plus the RN column

Oracle "LIMIT n,m" equivalent

1 Answers1

Related