概要
- PostgreSQL や MySQL で使える LIMIT 句が Oracle Database には無い
- LIMIT 句のような機能として Oracle Database SQL の FETCH FIRST ROWS ONLY, ROWNUM, ROW_NUMBER の使用例を挙げる
- 検証環境: Oracle Database 12c Release 2 (12.2.0.1.0) Enterprise Edition (on Docker) + SQL*Plus: Release 12.2.0.1.0 Production + macOS Catalina
FETCH FIRST ROWS ONLY
基本形
FETCH FIRST 行数 ROWS ONLY
公式資料
row_limiting_clauseを使用すると、問合せから返される行数を制限できます。オフセットおよび戻される行の数または割合を指定できます。この句は、上位N番のレポートを実装するために使用できます。結果の一貫性を保つには、order_by_clauseを指定して、ソート順序を決定的にします。
3行取得
SQL> SELECT * FROM foo_names FETCH FIRST 3 ROWS ONLY;
ID NAME
---------- ------------------------------------------------
1 Alice
2 Bob
3 Carol
条件指定して3行取得
SQL> SELECT * FROM foo_names WHERE id <= 20 ORDER BY id DESC FETCH FIRST 3 ROWS ONLY;
ID NAME
---------- ------------------------------------------------
20 Trent
19 Sybil
18 Rupert
10行スキップして3行取得
SQL> SELECT * FROM foo_names ORDER BY id OFFSET 10 ROWS FETCH FIRST 3 ROWS ONLY;
ID NAME
---------- ------------------------------------------------
11 Kendrick
12 Luther
13 Mallory
ROWNUM
基本形
WHERE ROWNUM <= 行数
公式資料
ROWNUM疑似列は、問合せによって戻される各行について、表や結合処理された行の集合からOracleが行を選択する順序を示す番号を戻します。つまり、選択される最初の行のROWNUMは1、2番目の行のROWNUMは2です(以降同様に続きます)。
3行取得
SQL> SELECT * FROM foo_names WHERE ROWNUM <= 3;
ID NAME
---------- ------------------------------------------------
1 Alice
2 Bob
3 Carol
条件指定して3行取得
SQL> SELECT * FROM (SELECT * FROM foo_names WHERE id <= 20 ORDER BY id DESC) WHERE ROWNUM <= 3;
ID NAME
---------- ------------------------------------------------
20 Trent
19 Sybil
18 Rupert
11行目から13行目を取得
SQL> SELECT id, name FROM (SELECT foo_names.*, ROWNUM AS rn FROM foo_names ORDER BY id) WHERE rn BETWEEN 11 AND 13;
ID NAME
---------- ------------------------------------------------
11 Kendrick
12 Luther
13 Mallory
ROW_NUMBER
基本形
ROW_NUMBER() OVER (ORDER BY column_name) rn FROM table_name) WHERE rn <= 行数;
公式資料
ROW_NUMBERは分析ファンクションです。このファンクションは、order_by_clauseに指定された行の、1から始まる順番順序で、このファンクションが適用される各行(パーティションの各行、または問合せが戻す各行)に一意の数値を割り当てます。
3行取得
SQL> SELECT id, name FROM (SELECT foo_names.*, ROW_NUMBER() OVER (ORDER BY id) rn FROM foo_names) WHERE rn <= 3;
ID NAME
---------- ------------------------------------------------
1 Alice
2 Bob
3 Carol
条件指定して3行取得
SQL> SELECT id, name FROM (SELECT foo_names.*, ROW_NUMBER() OVER (ORDER BY id DESC) rn FROM foo_names WHERE id <= 20) WHERE rn <= 3;
ID NAME
---------- ------------------------------------------------
20 Trent
19 Sybil
18 Rupert
11行目から13行目を取得
SQL> SELECT id, name FROM (SELECT foo_names.*, ROW_NUMBER() OVER (ORDER BY id) rn FROM foo_names) WHERE rn BETWEEN 11 AND 13;
ID NAME
---------- ------------------------------------------------
11 Kendrick
12 Luther
13 Mallory