What is the dense rank?

Showing Answers 1 - 2 of 2 Answers


  • May 13th, 2006

The DENSE_RANK function computes the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.

The DENSE_RANK function does not skip numbers and will assign the same number to those rows with the same value. Hence, after the result set is built in the inline view, we can simply select all of the rows with a dense rank of three or less, this gives us everyone who makes the top three salaries by department number.


Give me the set of sales people who make the top 3 salaries - that is, find the set of distinct salary amounts, sort them, take the largest three, and give me everyone who makes one of those values.

  SELECT deptno, ename, sal,
  OVER (
    PARTITION BY deptno ORDER BY sal desc
  ) TopN FROM emp
WHERE TopN <= 3
ORDER BY deptno, sal DESC

    DEPTNO ENAME             SAL       TOPN
---------- ---------- ---------- ----------
        10 KING             5000          1
           CLARK            2450          2
           MILLER           1300          3

20 SCOTT            3000          1  <--- ! (in case of rank  JONES will have 3 and dense rank will have 2)
           FORD             3000          1  <--- !

           JONES            2975          2
           ADAMS            1100          3

        30 BLAKE            2850          1
           ALLEN            1600          2
        30 TURNER           1500          3

Rahul B

  • Jan 17th, 2018

Rank - After same ranking , next ranks are skipped e.g. 1,2,2,4,5,6,6,6,6....
Dense_rank - After same ranking also ranking continues e.g. 1,2,2,3,4,5,6,6,6,7....

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions