MaxCompute:DENSE_RANK

Syntax

bigint dense_rank() over ([partition_clause] [orderby_clause])

Parameters

For the full syntax of partition_clause and orderby_clause, see Window functions.

Return value

Returns a BIGINT value. Ranks start at 1 and increment by 1 each time the ORDER BY value changes — with no gaps. Rows with equal ORDER BY values in the same partition share the same rank.

If orderby_clause is not specified, all rows return 1.

Unlike RANK, DENSE_RANK never skips rank values after a tie. Unlike ROW_NUMBER, DENSE_RANK assigns the same rank to tied rows.

Limits

• Window functions are supported only in SELECT statements.

• A window function cannot contain nested window functions or nested aggregate functions.

• Window functions and aggregate functions of the same level cannot appear in the same query.

Example

The following example ranks employees within each department by salary in descending order.

Sample data

CREATE TABLE IF NOT EXISTS emp (
  empno    BIGINT,
  ename    STRING,
  job      STRING,
  mgr      BIGINT,
  hiredate DATETIME,
  sal      BIGINT,
  comm     BIGINT,
  deptno   BIGINT
);

Load sample data from a local file:

tunnel upload emp.txt emp;
-- Replace emp.txt with the actual path to your data file.

The emp.txt file contains the following data:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Query

SELECT deptno, ename, sal,
  dense_rank() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
FROM emp;

Output

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 3          |
| 20         | SMITH      | 800        | 4          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 5          |
+------------+------------+------------+------------+

In department 10, JACCKA and KING both earn 5000, so both receive rank 1. The next rank is 2 (not 3), because DENSE_RANK produces no gaps. In department 30, MARTIN and WARD both earn 1250 and share rank 4; JAMES receives rank 5.

Related functions

DENSE_RANK is a window function. For other functions that aggregate or rank data over a window, see Window functions.