Select TOP 10 Oracle
Ngày đăng:
09/02/2022
Trả lời:
0
Lượt xem:
28
Show
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 clauseOther 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 dataFirst, 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 onlyThe 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 onlyThe 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 tiesWith 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 recordWith 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 rowsWith 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 -- rownumIn Oracle 11g, the rownum pseudo column was needed. The wrong wayThe 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 queryThe 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 againUnfortunately, 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 functionsPaging, 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 updrop table top_n_test purge;Github respository oracle-patterns, path: /SQL/select/top_n/clean-up.sql ThanksMany thanks to Oche Ejembi who spotted and fixed an error on this page. See alsoSQL: select first row only The fetch first n rows clause was introduced with SQL:2011. Index |