[Oracle] 擬似列ROWNUMの利用

Oracleにはクエリーの結果セットの行番号を表す擬似列ROWNUMというものがあります。

ROWNUMは以下のような場合に使用します。

• クエリーの結果セットのレコードに行番号を付ける。
• 取得件数を先頭からｎ行に絞り込む。

行番号の表示

以下のようにカラムリストに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

先頭からｎ行を取得する

以下のように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