Dá uma olhada nesse exemplo do scott:
Selecionar tudo
SELECT deptno, ename, sal ,
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
Aí vai a resposta dele:
Selecionar tudo
SQL> SELECT deptno, ename, sal ,
2 ROW_NUMBER()
3 OVER (PARTITION BY deptno
4 ORDER BY ENAME) "Seq"
5 FROM emp
6 ORDER BY deptno, ename
7 /
DEPTNO ENAME SAL Seq
---------- ---------- ---------- ----------
10 CLARK 2450 1
10 KING 5000 2
10 MILLER 1300 3
20 ADAMS 1100 1
20 FORD 3000 2
20 JONES 2975 3
20 SCOTT 3000 4
20 SMITH 800 5
30 ALLEN 1600 1
30 BLAKE 2850 2
30 JAMES 950 3
30 MARTIN 1250 4
30 TURNER 1500 5
30 WARD 1250 6
14 rows selected.
SQL>
Dá uma olhada nesse tópico sobre funções analíticas:
http://glufke.net/oracle/viewtopic.php?p=2028