OracleでLimit句を使ったかのようなSQLを実行しよう

　Oracle でも MySQL や PostgreSQL みたく、SQL で LIMIT句が使えないかな？ とちょっと調べてみました。

　結論としては、Oracle では LIMIT句そのものは使えないのですが、ちょいとSQL文を工夫することで、同様の結果が得る事ができました。

というわけで、メモメモφ（．．）。

EMPNO	ENAME
7369	SMITH
7499	ALLEN
7521	WARD
7566	JONES
7654	MARTIN
7698	BLAKE
7782	CLARK
7788	SCOTT
7839	KING
7844	TURNER
7876	ADAMS
7900	JAMES
7902	FORD
7934	MILLER

　例えば、左のような EMP テーブルがあったとします。このデータから、EMPNO の大きい方から２行を取得したいわけですよ。

これが MySQL や PostgreSQL なら、

　　select * from EMP order by EMPNO desc limit 2

で一発というシュチュエーションです。

　さて、じゃぁ Oracle ではどうやるんだ？ て話しなんですが、ROWNUM というカラム（？）を使います。上記の SQL を Oracle 用に書き直すと、

select ROWNUM, EMPNO, ENAME from ( select EMPNO, ENAME from EMP order by EMPNO desc ) where ROWNUM<=2

となり、帰ってくる結果は

ROWNUM	EMPNO	ENAME
1	7934	MILLER
2	7902	FORD

のようになります。

　ここで、元のテーブルに無い ROWNUM ってカラムは何なんだ？ どこから出てきた？ という疑問が湧きますが、「Oracle がクエリーの結果に自動で追加してくれる行番号」 みたいな理解でいいと思います。

　お次はもうちょっと難しく、EMPNO の大きい方から３行目と４行目を取得しましょう。SQL は以下のように変わります。

select ROWNUM, RN, EMPNO, ENAME from ( select ROWNUM as RN, EMPNO, ENAME from EMP order by EMPNO desc ) where RN>2 and RN<5

そして結果の方は、

ROWNUM	RN	EMPNO	ENAME
1	3	7900	JAMES
2	4	7876	ADAMS

となります。内側の select文で帰ってくる結果セットの ROWNUM に、別名を付けて情報を保持させているのがミソですね。

ちなみに、 MySQL や PostgreSQL だと、

　　select * from EMP order by EMPNO desc limit 3,2

という SQL文になります。

　ただ Oracle はその仕様において、order by を使用していないクエリーの結果の行の並び順は保証していないので、厳密に並び順が重要となる場合は、

select ROWNUM, RN, EMPNO, ENAME from ( select ROWNUM as RN, EMPNO, ENAME from EMP order by EMPNO desc ) where RN>2 and RN<5 order by RN

のようにしておいた方がいいのかも。

なお動作確認は、Wondows Server 2008 上の Oracle 10g（10.2.0.1）にて行いました。

参照リンク
　・OracleではLimit句が使えない | yukotan hour
　・Oracleでlimit offsetもどき - よねのはてな