Purpose
This function calculates the rank of a value within a set of values. When there are duplicate values in the ranking, the same rank is assigned, and the number of rows with the same value is recorded in the next rank. You can use this function as an aggregate or analytic function.
Note
- As an aggregate function,
RANKcalculates the rank of the assumed rows identified by the parameterexprrelative to the specified sorting specification. The parameter expressionsexprand the expressions in theORDER BYclause of the aggregate function must match by position. Therefore, the number of parameters must be the same, and their data types must be compatible. - As an analytic function,
RANKcalculates the rank of each row returned by the query relative to other rows returned by the query, based on the values specified invalue_exprsandorder_by_clause.
Syntax
/*Aggregate syntax*/
RANK(expr [, expr ]...) WITHIN GROUP
( ORDER BY
expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]
[, expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...
)
/*Analytic syntax*/
RANK() OVER ([ query_partition_clause ] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | Specifies the value to query. |
| expr_col | Specifies the column name corresponding to the value to query. |
| DESC | ASC | Specifies the sorting method, which is optional.
|
| NULLS { FIRST | LAST } | Specifies the position of NULL values in expr_col after sorting, which is optional.
|
| OVER | Defines the window for calculation using the OVER clause. For more information, see Analytic Function Description. |
Return type
Returns 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
Example of an aggregate function
Returns the rank of 3300 in the sal column.
obclient> SELECT RANK(3300) WITHIN GROUP (ORDER BY sal) FROM emp_msg;
+-----------------------------------+
| RANK(3300)WITHINGROUP(ORDERBYSAL) |
+-----------------------------------+
| 13 |
+-----------------------------------+
1 row in set
Example of an analytic function
Groups by the deptno column and sorts the sal column in descending order, then returns the rank of each value in the sal column.
obclient> SELECT deptno,ename,sal,
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 | 3 |
| 20 | ADAMS | 1400 | 4 |
| 20 | SMITH | 1100 | 5 |
| 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 | 7 |
+--------+--------+------+------+
15 rows in set