RANK & DENSE_RANK

RANK calculates the rank of a value in a group of values. The return type is NUMBER. The ranks may not be consecutive number.

DENSE_RANK computes the rank of a row in an ordered group of rows . The ranks are consecutive integers beginning with 1. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

Q, Give me the set of sales people making the top 3 salaries in each department"? scott@TKYTE816> break on deptno skip 1 scott@TKYTE816> select * 2 from ( select deptno, ename, sal, 3 dense_rank() over ( partition by deptno 4 order by sal desc ) dr 5 from emp ) 6 where dr <= 3 7 order by deptno, sal desc 8 / DEPTNO ENAME SAL DR ---------- ---------- ---------- ---------- 10 KING 5000 1 CLARK 2450 2 MILLER 1300 3 20 SCOTT 3000 1 FORD 3000 1 JONES 2975 2 ADAMS 1100 3 30 BLAKE 2850 1 ALLEN 1600 2 TURNER 1500 3