All Products
Search
Document Center

DENSE_RANK

Last Updated: Jun 18, 2021

DENSE_RANK calculates the rank of a row in an ordered group of rows and returns the rank as NUMBER. Ranks are consecutive integers that start from 1. The maximum rank value is the number of unique values that are returned by the query. Rank values are not skipped in the case of ties. The rows that have same values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

When DENSE_RANK serves as an analytic function, it calculates the rank of each row that is returned by the query among other rows based on the value of value_exprs in order_by_clause.

Syntax

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

Return type

The return value is of the NUMBER data type.

Examples

To create the emp_msg table and insert data into the table, execute the following statements:

CREATE TABLE emp_msg(deptno INT, ename varchar(30), sal INT, MGR varchar(30));
INSERT INTO emp_msg VALUES(10,'CLARK', 2750, 7839);       
INSERT INTO emp_msg VALUES(10,'KING', 5300, NULL);       
INSERT INTO emp_msg VALUES(10,'MILLER', 1600, 7782);        
INSERT INTO emp_msg VALUES(20,'ADAMS', 1400, 7788);       
INSERT INTO emp_msg VALUES(20,'FORD', 3300, 7566);      
INSERT INTO emp_msg VALUES(20,'JONES', 3275, 7839);      
INSERT INTO emp_msg VALUES(20,'SCOTT', 3300, 7566);    
INSERT INTO emp_msg VALUES(20,'SMITH', 1100, 7902);   
INSERT INTO emp_msg VALUES(30,'ALLEN', 1900, 7698); 
INSERT INTO emp_msg VALUES(30,'BLAKE', 3150, 7839); 
INSERT INTO emp_msg VALUES(30,'JAMES', 1250, 7698); 
INSERT INTO emp_msg VALUES(30,'MARTIN', 1550, 7698);
INSERT INTO emp_msg VALUES(30,'TURNER', 1800, 7698);
INSERT INTO emp_msg VALUES(30,'WARD', 1550, 7698);

The following example shows the analytic function feature. Execute the following statement:

SELECT deptno, ename, sal, DENSE_RANK ( ) OVER ( partition BY deptno ORDER BY sal DESC ) "RANK"
FROM emp_msg WHERE sal>2000;

The following query result is returned:

+--------+-------+------+------+
| DEPTNO | ENAME | SAL  | RANK |
+--------+-------+------+------+
|     10 | KING  | 5300 |    1 |
|     10 | CLARK | 2750 |    2 |
|     20 | SCOTT | 3300 |    1 |
|     20 | FORD  | 3300 |    1 |
|     20 | JONES | 3275 |    2 |
|     30 | BLAKE | 3150 |    1 |
+--------+-------+------+------+