The DENSE_RANK function calculates consecutive rankings in database queries.
Limits
Before you use window functions, take note of the following limits:
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
You cannot use window functions together with aggregate functions of the same level.
Syntax
bigint dense_rank() over ([partition_clause] [orderby_clause])Description
Calculates the rank of the current row within a partition based on the orderby_clause. The rank counts from 1. Rows with the same order by values in a partition have the same rank. Whenever the order by value changes, the rank is incremented by 1 based on the maximum rank of the previous group.
Parameters
partition_clause and orderby_clause: For more information, see windowing_definition.
Return value
A value of the BIGINT type is returned. If orderby_clause is not specified, all returned values are 1.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path (path and name) to which you upload the data file.The emp.txt file contains the following sample 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,,10Examples
Group all employees based on the deptno column. In each group, sort the employees in descending order based on the sal value to obtain the ordinal numbers of the employees in their own groups. Sample statement:
select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;The following figure shows the sample 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 |
+------------+------------+------------+------------+Related functions
DENSE_RANK is a window function. For more information about functions that sum data or sort values for specified window columns, see Window functions.