[sql] How do I limit the number of rows returned by an Oracle query after ordering?
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.
Is there a way to make an
Oracle query behave like it contains a
MySQL limit clause?
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.
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.
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.
(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.
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
Select * from XYZ a order by a.pqr FETCH FIRST 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:
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.