ROW_NUMBER()関数を使うと結果セットに対して行番号を振ることができますが、PARTITION BY句を使うと、PARTITION BY句で指定した列の値ごとに行番号が振られます。
例えば、部署（deptno）ごとに給料（sal）の多い者順に行番号を振る場合、以下のようになります。
SELECT DEPTNO, EMPNO, ENAME, SAL, ROW_NUMBER()
OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) ROW#
FROM EMP;
DEPTNO EMPNO ENAME SAL ROW#
---------- ---------- -------------------- ---------- ----------
10 7839 KING 5000 1
10 7782 CLARK 2450 2
10 7934 MILLER 1300 3
20 7902 FORD 3000 1
20 7566 JONES 2975 2
20 7369 SMITH 800 3
30 7698 BLAKE 2850 1
30 7499 ALLEN 1600 2
30 7844 TURNER 1500 3
30 7521 WARD 1250 4
30 7654 MARTIN 1250 5
30 7900 JAMES 950 6
行番号でなく、ランキングを表示したい場合は、RANK()を使います。
SELECT DEPTNO, EMPNO, ENAME, SAL, RANK()
OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) ROW#
FROM EMP;
DEPTNO EMPNO ENAME SAL ROW#
---------- ---------- -------------------- ---------- ----------
10 7839 KING 5000 1
10 7782 CLARK 2450 2
10 7934 MILLER 1300 3
20 7902 FORD 3000 1
20 7566 JONES 2975 2
20 7369 SMITH 800 3
30 7698 BLAKE 2850 1
30 7499 ALLEN 1600 2
30 7844 TURNER 1500 3
30 7521 WARD 1250 4
30 7654 MARTIN 1250 4
30 7900 JAMES 950 6
RANK()の場合、同値の場合に同じ番号になるというところがROW_NUMBER()と異なります。
また、このように番号を付けた上で、指定した番号の範囲のレコードだけを抽出したい場合は以下のようにします。
SELECT * FROM (
SELECT DEPTNO, EMPNO, ENAME, SAL, ROW_NUMBER()
OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) ROW#
FROM EMP
)
WHERE ROW# BETWEEN 2 AND 5;
DEPTNO EMPNO ENAME SAL ROW#
---------- ---------- -------------------- ---------- ----------
10 7782 CLARK 2450 2
10 7934 MILLER 1300 3
20 7566 JONES 2975 2
20 7369 SMITH 800 3
30 7499 ALLEN 1600 2
30 7844 TURNER 1500 3
30 7521 WARD 1250 4
30 7654 MARTIN 1250 5
この場合、2番目から5番目を抽出しています。【関連エントリ】
[Oracle] 擬似列ROWNUMの利用 2004/09/16
[Oracle] ROW_NUMBER()を使った行番号表示の応用 2004/10/20