Purpose
DENSE_RANK() returns the rank of a row in an ordered group of rows. The values of rank are consecutive integers starting from 1. The largest rank value is the number of unique values returned by the query. Rows with equal values for the ranking criteria receive the same rank. You can use this function as an aggregate or analytic function.
Note
- As an aggregate function,
DENSE_RANK()calculates the density level of the hypothetical rows identified by the arguments of the function based on the specified ordering specification. The constant argument expressionexprmatches to the expression in theorder_by_clauseaggregate clause by position. Therefore, the number of arguments must be the same and their types must be compatible. - As an analytic function,
DENSE_RANK()calculates the rank of each row returned from the query relative to other rows based onvalue_exprsinorder_by_clause.
Syntax
/*Aggregate syntax*/
DENSE_RANK(expr [, expr ...])
WITHIN GROUP ( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]
[,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...
)
/*Analytic syntax*/
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The value of the data type in the corresponding column. |
| expr_col | The column name of the value to query. |
| DESC | ASC | Optional. The sorting method of the list.
|
| NULLS { FIRST | LAST } | Optional. The position of NULL in expr_col after sorting.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
The return type is NUMBER.
Examples
The table emp_msg has been created.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
Example of an aggregate function
Return the rank of 3300 in the sal column.
obclient> SELECT DENSE_RANK(3300) WITHIN GROUP (ORDER BY sal) FROM emp_msg;
+-----------------------------------------+
| DENSE_RANK(3300)WITHINGROUP(ORDERBYSAL) |
+-----------------------------------------+
| 12 |
+-----------------------------------------+
1 row in set
Example of an analytic function
Group the data records in the table by the deptno column and sort the data records by the sal column in descending order. Return the rank of each value in the sal column.
obclient> SELECT deptno,ename, sal,
DENSE_RANK ( ) OVER (PARTITION BY deptno ORDER BY sal DESC ) "RANK"
FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | RANK |
+--------+--------+------+------+
| 10 | KING | 5300 | 1 |
| 10 | CLARK | 2750 | 2 |
| 10 | MILLER | 1600 | 3 |
| 20 | FORD | 3300 | 1 |
| 20 | SCOTT | 3300 | 1 |
| 20 | JONES | 3275 | 2 |
| 20 | ADAMS | 1400 | 3 |
| 20 | SMITH | 1100 | 4 |
| 30 | BLAKE | 3150 | 1 |
| 30 | ALLEN | 1900 | 2 |
| 30 | TURNER | 1800 | 3 |
| 30 | SCLARK | 1750 | 4 |
| 30 | MARTIN | 1550 | 5 |
| 30 | WARD | 1550 | 5 |
| 30 | JAMES | 1250 | 6 |
+--------+--------+------+------+
15 rows in set
