Purpose
This function calculates the rank of rows within an ordered group of rows. The rank values are continuous integers starting from 1, and the maximum rank value is the number of rows that meet the query result. Rows with the same value in the specified sorting criteria receive the same rank, and the same number of rows will not be recorded in the next rank. It can be used as an aggregate or analytical function.
Note
- As an aggregate function,
DENSE_RANKcalculates the dense rank of the assumed rows identified by the function's parameters based on the specified sorting criteria. Constant parameter expressionsexprandorder_by_clausein the aggregate function match by position. Therefore, the number of parameters must be the same and the types must be compatible. - As an analytical function,
DENSE_RANKcalculates the rank of each row returned by the query relative to other rows based on the values ofvalue_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 } ]]...
)
/*Analytical syntax*/
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The value of the corresponding column. |
| expr_col | The name of the column corresponding to the value to be queried. |
| DESC | ASC | Specifies the sorting order. This is an optional parameter.
|
| NULLS { FIRST | LAST } | Specifies the position of NULL values in expr_col after sorting. This is an optional parameter.
|
| OVER | Specifies the window for calculation using the OVER clause. For more information, see Analytical Function Description. |
Return type
The return value is of the NUMBER data type.
Examples
Assume that the emp_msg table 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
Aggregate function example
Returns 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
Analytical function example
Groups by the deptno column and sorts the rows in descending order of the sal column. Returns 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