Hey Guys,
RANK() : function always returns sequential order for each record.
DENSE_RANK() : It returns continuous order for each record.
SELECT empno,
deptno,
sal,
mgr,
RANK () OVER (PARTITION BY deptno ORDER BY sal) "rank",
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "dense rank"
FROM emp;
RANK() : function always returns sequential order for each record.
DENSE_RANK() : It returns continuous order for each record.
SELECT empno,
deptno,
sal,
mgr,
RANK () OVER (PARTITION BY deptno ORDER BY sal) "rank",
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "dense rank"
FROM emp;
| EMPNO | DEPTNO | SAL | MGR | RANK | DENSE RANK |
|---|---|---|---|---|---|
| 7934 | 10 | 1300 | 7782 | 1 | 1 |
| 7782 | 10 | 2450 | 7839 | 2 | 2 |
| 7839 | 10 | 5000 | 3 | 3 | |
| 7369 | 20 | 800 | 7902 | 1 | 1 |
| 7876 | 20 | 1100 | 7788 | 2 | 2 |
| 7566 | 20 | 2975 | 7839 | 3 | 3 |
| 7788 | 20 | 3000 | 7566 | 4 | 4 |
| 7902 | 20 | 3000 | 7566 | 4 | 4 |
| 7900 | 30 | 950 | 7698 | 1 | 1 |
| 7521 | 30 | 1250 | 7698 | 2 | 2 |
| 7844 | 30 | 1250 | 7698 | 2 | 2 |
| 7654 | 30 | 1250 | 7698 | 2 | 2 |
| 7499 | 30 | 1600 | 7698 | 5 | 3 |
| 7698 | 30 | 2850 | 7839 | 6 | 4 |



No comments:
Post a Comment
Please do not add any spam links or abusive comments.