ROWNUMは以下のような場合に使用します。
- クエリーの結果セットのレコードに行番号を付ける。
- 取得件数を先頭からn行に絞り込む。
行番号の表示
以下のようにカラムリストにROWNUMを加えるだけです。SQL> SELECT ROWNUM, EMPNO, ENAME FROM EMP; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 1 7369 SMITH 2 7499 ALLEN 3 7521 WARD 4 7566 JONES 5 7654 MARTIN 6 7698 BLAKE 7 7782 CLARK 8 7788 SCOTT 9 7839 KING 10 7844 TURNER 11 7876 ADAMS ROWNUM EMPNO ENAME ---------- ---------- -------------------- 12 7900 JAMES 13 7902 FORD 14 7934 MILLER
行番号の表示(ソートを含む場合)
ソートを含むクエリーの場合、行番号はソート前に振られるためソート後は以下のようにぐちゃぐちゃになります。SQL> SELECT ROWNUM, EMPNO, ENAME FROM EMP ORDER BY ENAME; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 11 7876 ADAMS 2 7499 ALLEN 6 7698 BLAKE 7 7782 CLARK 13 7902 FORD 12 7900 JAMES 4 7566 JONES 9 7839 KING 5 7654 MARTIN 14 7934 MILLER 8 7788 SCOTT 1 7369 SMITH 10 7844 TURNER 3 7521 WARD
これを解消するにはROWNUMではなく、ROW_NUMBER()という関数を使います。
SQL> SELECT ROW_NUMBER() OVER ( ORDER BY ENAME ) AS ROWNUM#, EMPNO, ENAME FROM EMP; ROWNUM# EMPNO ENAME ---------- ---------- -------------------- 1 7876 ADAMS 2 7499 ALLEN 3 7698 BLAKE 4 7782 CLARK 5 7902 FORD 6 7900 JAMES 7 7566 JONES 8 7839 KING 9 7654 MARTIN 10 7934 MILLER 11 7788 SCOTT 12 7369 SMITH 13 7844 TURNER 14 7521 WARD
この構文になじめないのであれば、ちょっと強引ですが以下のようなやり方もあります。
SQL> SELECT ROWNUM, EMPNO, ENAME FROM ( SELECT EMPNO, ENAME FROM EMP ORDER BY ENAME ); ROWNUM EMPNO ENAME ---------- ---------- -------------------- 1 7876 ADAMS 2 7499 ALLEN 3 7698 BLAKE 4 7782 CLARK 5 7902 FORD 6 7900 JAMES 7 7566 JONES 8 7839 KING 9 7654 MARTIN 10 7934 MILLER 11 7788 SCOTT 12 7369 SMITH 13 7844 TURNER 14 7521 WARD
先頭からn行を取得する
以下のようにWHERE句にROWNUMの条件を加えます。
SQL> SELECT EMPNO, ENAME FROM EMP WHERE ROWNUM <=5; EMPNO ENAME ---------- -------------------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN
ソートを含めたい場合は、以下のような感じです。
SQL> SELECT EMPNO, ENAME FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY ENAME ) AS R OWNUM#,EMPNO,ENAME FROM EMP ) WHERE ROWNUM# <= 5;
または
SQL> SELECT EMPNO, ENAME FROM ( SELECT EMPNO, ENAME FROM EMP ORDER BY ENAME ) WH ERE ROWNUM <=5; EMPNO ENAME ---------- -------------------- 7876 ADAMS 7499 ALLEN 7698 BLAKE 7782 CLARK 7902 FORD
【関連エントリ】
[Oracle] 擬似列ROWNUMの利用 2004/09/16
[Oracle] ROW_NUMBER()を使った行番号表示の応用 2004/10/20