How do I limit the number of rows returned by an Oracle query after ordering?
oracle, pagination, sql, sql-limit
Solution
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.
Problem
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.