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
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');
Select first row only
The select first row only does exactly that:
select *
from
top_n_test
order by
num
fetch first row only;
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;
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;
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;
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;
NUM TXT LN
---------- ---------- --
1 one en
2 deux fr
3 three en
11g: ROWNUM
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;
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;
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;
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;
NUM TXT LN
---------- ---------- --
3 drei de
4 vier de
5 cinque fr