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.