Purpose
You can call this function to calculate 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 it as an aggregate or analytic function.
Note
- As an aggregate function,
DENSE_RANKcalculates 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 analysis function,
DENSE_RANKcalculates 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 about analytic functions, see Analytic functions. |
Return types
This function returns data of the NUMBER data type.
Examples
Assume that you have created the table emp_msg.
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 example
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
Analytic example
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