Sample query:
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.
How to 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;Answer #2:
tarting 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.
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 the 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.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
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.
Answer #5:
On Oracle 12c :
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;How to limit the number of rows returned by an Oracle query after ordering?
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 <= 50Pagination 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; Hope you learned something from this post.
Follow Programming Articles for more!