Search notes:
Oracle SQL: select first n rows / rows between n and m [top n/limit queries]
At times, it's necessary to select the first n rows or the rows between n and m [paging] from a table or query.
Here are a few wrong and correct ways to do it.
12c - row limiting clause
Other databases made top-n queries quite easy, with, for example, a limit clause [such as SQLite or a top n clause in SQL Server].
Oracle, however, did not have a similar clause until 12c, what I found, imho, quite a nuisance.
12c, finally, comes with the row limiting clause which makes it acceptably easy to query the first n records.
test data
First, we need to create a table with test data:
create table top_n_test [
num number [ 2],
txt varchar2[10],
lng varchar2[ 2] not null check [lng in ['en', 'de', 'fr']]
];
insert into top_n_test values [4, 'vier' , 'de'];
insert into top_n_test values [1, 'one' , 'en'];
insert into top_n_test values [6, 'six' , 'en'];
insert into top_n_test values [3, 'three' , 'en'];
insert into top_n_test values [8, 'acht' , 'de'];
insert into top_n_test values [9, 'nine' , 'en'];
insert into top_n_test values [2, 'deux' , 'fr'];
insert into top_n_test values [7, 'seven' , 'en'];
insert into top_n_test values [3, 'drei' , 'de'] /* Note: second record with num=3 */;
insert into top_n_test values [5, 'cinque' , 'fr'];
Github respository oracle-patterns, path: /SQL/select/top_n/data.sql
select first row only
The select first row only does exactly that:
select *
from
top_n_test
order by
num
fetch first row only;
Github respository oracle-patterns, path: /SQL/select/top_n/12c-first-row.sql
NUM TXT LN
---------- ---------- --
1 one en
select first n rows only
The select first n rows only selects the first n rows.
In the following example, there's an ambiguity: the third row might be where the num 3 is translated to german [drei] or where it is english [three].
select *
from
top_n_test
order by
num
fetch first 3 rows only;
Github respository oracle-patterns, path: /SQL/select/top_n/12c-first-3-rows.sql
NUM TXT LN
---------- ---------- --
1 one en
2 deux fr
3 three en
select first 3 rows with ties
With select first n rows with ties, this ambiguity can be resolved
select *
from
top_n_test
order by
num
fetch first 3 rows with ties;
Github respository oracle-patterns, path: /SQL/select/top_n/12c-first-3-rows-with-ties.sql
NUM TXT LN
---------- ---------- --
1 one en
2 deux fr
3 three en
3 drei de
select m rows starting at the nth record
With offset m rows fetch next n rows only, the n records, starting at the mth record, can be fetched:
select *
from
top_n_test
order by
num
offset 4 rows
fetch next 2 rows only;
Github respository oracle-patterns, path: /SQL/select/top_n/12c-offset-4-next-2.sql
NUM TXT LN
---------- ---------- --
4 vier de
5 cinque fr
Select a percentage rather than a fixed amount of rows
With fetch first n percent rows only, it can be stated how many percent of the result set should be returned:
select *
from
top_n_test
order by
num
fetch first 30 percent rows only;
Github respository oracle-patterns, path: /SQL/select/top_n/12c-30-pct.sql
11g -- rownum
In Oracle 11g, the rownum pseudo column was needed.
The wrong way
The following approach is [most probably] wrong [and returns something different than was intended] because Oracle first evaluates the where clause, then adds the pseudo column rownum and then applies the order by. [See also: SQL: Order of select operations].
select *
from
top_n_test
where
rownum < 4
order by
num;
Github respository oracle-patterns, path: /SQL/select/top_n/rownum-wrong.sql
NUM TXT LN
---------- ---------- --
1 one en
4 vier de
6 six en
Inner query
The following approach is better. With the inner query, Oracle is forced to first evaluate the order by clause. The result set is then passed to the outer query where the where rownum condition is applied.
select * from [
select *
from
top_n_test
order by
num
]
where
rownum < 4;
Github respository oracle-patterns, path: /SQL/select/top_n/rownum-inner-query.sql
NUM TXT LN
---------- ---------- --
1 one en
2 deux fr
3 three en
Paging - wrong again
Unfortunately, the approach with the inner query does not allow for paging [selecting rows n through m]:
select * from [
select *
from
top_n_test
order by
num
]
where
rownum between 4 and 6;
Github respository oracle-patterns, path: /SQL/select/top_n/rownum-paging.sql
This query returns nothing!
This is because rownum never reaches 4 because rownum only increases when a row is returned.
Paging with analytical functions
Paging, however, is possible with analytical functions, such as row_number[], rank[] or dense_rank[].
select
num,
txt,
lng
from [
select
t.*,
row_number[] over [order by num] r
from
top_n_test t
]
where
r between 4 and 6;
Github respository oracle-patterns, path: /SQL/select/top_n/row_number-paging.sql
NUM TXT LN
---------- ---------- --
3 drei de
4 vier de
5 cinque fr
Cleaning up
drop table top_n_test purge;Github respository oracle-patterns, path: /SQL/select/top_n/clean-up.sql
Thanks
Many thanks to Oche Ejembi who spotted and fixed an error on this page.
See also
SQL: select first row only
The fetch first n rows clause was introduced with SQL:2011.
Index