ORACLE の LIMIT OFFSET
業務でデータベースは PostgreSQL を使うことが多いですが ORACLE も使います。 その ORACLE が 12c になりました。 そう、12c になるということは LIMIT や OFFSET が使えるということです。 そんな嬉しさから書きます。
まずはデータの準備。
テーブルを作りたくないので 次の SQL でゴマかします。
SELECT * FROM (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL) ORDER BY RANK, ID
SQL> /
ID SCORE RANK
---------- ---------- ----------
3 98 1
6 95 2
1 85 3
4 80 4
5 80 4
7 75 6
2 70 7
このようにテストの点数と ランキングが入っています。
まずは LIMIT ですが ORACLE （というかSQL:2008）では LIMIT ではなく 次のような書き方をします。
FETCH FIRST 100 ROWS ONLY
ちなみに この書き方は PostgreSQL でもサポートされています。 （完全に同じではありません）
まずは 4 行だけ取得。
SELECT *
FROM (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 4 ROWS ONLY
SQL> /
ID SCORE RANK
---------- ---------- ----------
3 98 1
6 95 2
1 85 3
4 80 4
4 行取得できました。
取得元のデータには RANK が 4 のデータが 2 レコードあります。 4位タイということですね。 4位までをを取得しようとして 4位が 1人しか取得できないのは困りますよね。
そんなときは 次のように書きます。
SELECT * FROM (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL) ORDER BY RANK FETCH FIRST 4 ROWS WITH TIES
SQL> / ID SCORE RANK ---------- ---------- ---------- 3 98 1 6 95 2 1 85 3 4 80 4 5 80 4
これで 4 位までを取得することができました。
ここで注意しなくてはならないのは ORDER BY から ID 列を外していることです。
WITH TIES の仕様は “ソート順の最後の項目が同じであれば取得する”ということなので ID が最後ではダメなのです。 もし更に“同順位は ID でソートしたい”ということであれば もう 1 段階ラッピングする必要があります。
行の他に百分率で指定することもできます。
SELECT *
FROM (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 50 PERCENT ROWS ONLY
SQL> /
ID SCORE RANK
---------- ---------- ----------
3 98 1
6 95 2
1 85 3
4 80 4
50% を指定したので 7行中の 4行が取得できました。
次は OFFSET です。
次のような書き方をします。
OFFSET 10 ROWS
OFFSET 4 で 5行目から取得してみます。
SELECT *
FROM (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
OFFSET 4 ROWS
FETCH FIRST 4 ROWS ONLY
SQL> /
ID SCORE RANK
---------- ---------- ----------
5 80 4
7 75 6
2 70 7
5 行目から 3行取得できました。
これでページ送りなんかの機能も簡単に作れますね。
