RANK and
DENSE_RANK both provide rank to the records based on some column value or
expression. In case of a tie of 2 records at position N, RANK declares 2
positions N and skips position N+1 and gives position N+2 to the next record.
While DENSE_RANK declares 2 positions N but does not skip position N+1.
Query shows the usage of both RANK and
DENSE_RANK. For DEPTNO 20 there are two contenders for the first position
(EMPNO 7788 and 7902). Both RANK and DENSE_RANK declares them as joint toppers.
RANK skips the next value that is 2 and next employee EMPNO 7566 is given the
position 3. For DENSE_RANK there are no such gaps.
SELECT
empno, deptno, sal,
RANK()
OVER (PARTITION BY deptno
ORDER
BY sal DESC NULLS LAST) RANK,
DENSE_RANK()
OVER (PARTITION BY
deptno
ORDER BY sal DESC NULLS
LAST)
DENSE_RANK
FROM
emp
WHERE
deptno IN (10, 20)
ORDER
BY 2, RANK;
EMPNO DEPTNO
SAL RANK DENSE_RANK
------
------- ----- ----- ----------
7839
10 5000 1
1
7782
10 2450 2
2
7934
10 1300 3
3
7788
20 3000 1
1
7902
20 3000 1
1
7566
20 2975 3
2
7876
20 1100 4
3
7369
20 800 5
4
8
rows selected.
Query (RANK and
DENSE_RANK example)
No comments:
Post a Comment