Difference Between RANK() and DENSE_RANK() in SQL with an Example

There is a small difference between RANK() and DENSE_RANK() analytic functions. Lets take an example to illustrate this.

1. select ename,deptno,sal,RANK() OVER  (PARTITION BY deptno ORDER BY SAL) from emp;
The above query returns below results:

Rank() Query Result

2. select ename,deptno,sal,DENSE_RANK() OVER  (PARTITION BY deptno ORDER BY SAL) from emp;
The above query returns below results:

Dense_Rank() Query Result

Here DENSE_RANK() returns consecutive number rank like 1,2,2,3,4,5 for dept number ’30’.
But  RANK() returns rank numbers like 1,2,2,4,5,6. This is the main difference.

You can watch video tutorial about RANK() and DENSE_RANK() here:

Comments

comments

Leave a Reply