RANK is a window function that assigns ranking values to rows in a result set. Rows with the same values receive the same rank, and subsequent ranks skip the tied positions (for example: 1, 2, 2, 4). This means the ranks might not be consecutive.
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 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.
Parameters
partition_clause and orderby_clause: For more information, see windowing_definition.
Return value type
A value of the BIGINT type is returned. The return values may be repeated and non-consecutive. The specific return value is the rank assigned to the row within its group. Rows with the same sort values receive the same rank, and subsequent ranks skip positions. If orderby_clause is not specified, all rows are considered to have the same sort value, and all return 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,,10Sample statement
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, 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 | 3 |
| 10 | WELAN | 2450 | 3 |
| 10 | TEBAGE | 1300 | 5 |
| 10 | MILLER | 1300 | 5 |
| 20 | SCOTT | 3000 | 1 |
| 20 | FORD | 3000 | 1 |
| 20 | JONES | 2975 | 3 |
| 20 | ADAMS | 1100 | 4 |
| 20 | SMITH | 800 | 5 |
| 30 | BLAKE | 2850 | 1 |
| 30 | ALLEN | 1600 | 2 |
| 30 | TURNER | 1500 | 3 |
| 30 | MARTIN | 1250 | 4 |
| 30 | WARD | 1250 | 4 |
| 30 | JAMES | 950 | 6 |
+------------+------------+------------+------------+Related functions
RANK is a window function. For more information about functions that sum data or sort values in specified window columns, see Window functions.