Comparison and Selection of Rank and other functions in Oracle Database

Oracle DatabaseWhat are the differences between the Rank, Dense_Rank, and Row_Number functions? In practical applications, how should we choose? This article describes these in combination with examples.

First, create an employee information table and execute the following code in the query Analyzer:

 
 

  
  
Create Table EmployeeInfo (CODE Number(3) Not Null,EmployeeName varchar2(15),DepartmentID Number(3),Salary NUMBER(7,2),  
  
  
 
  
  
Constraint PK_EmployeeInfo Primary Key (CODE)); 
 
 

After the table is created, open the table in enterprise management and enter some information. To help you understand the entered content, I executed the query statement Select * From EMPLOYEEINFO; enter the employee information table as follows:

Execute the SQL statement:

 
 

  
  
Select EMPLOYEENAME,SALARY,  
  
  
 
  
  
RANK() OVER (Order By SALARY Desc)  "RANK",  
  
  
 
  
  
DENSE_RANK() OVER (Order By SALARY Desc ) "DENSE_RANK",  
  
  
 
  
  
ROW_NUMBER() OVER(Order By SALARY Desc) "ROW_NUMBER"  
  
  
 
  
  
 From EMPLOYEEINFO 
 
 

The result is as follows:

Note:The Rank, Dense_rank, and Row_number functions generate a natural number from 1 to N for each record. The value of N may be less than or equal to the total number of records. The only difference between the three functions is the ranking strategy when the same data is encountered.

① ROW_NUMBER:

The Row_number function returns a unique value. When the same data is encountered, the ranking increases sequentially according to the record sequence in the record set.

② DENSE_RANK:

The Dense_rank function returns a unique value, except that when the same data is encountered, the ranking of all the same data is the same.

③ RANK:

The Rank function returns a unique value. Unless the same data is encountered, the ranking of all the same data is the same, at the same time, the ranking between the last record and the next record is empty.

At the same time, it can also be sorted by group, that is, adding Partition by groupField in the Over clause:

 
 

  
  
Select DEPARTMENTID,EMPLOYEENAME,SALARY,  
  
  
 
  
  
RANK() OVER ( Partition By DEPARTMENTID Order By SALARY Desc)  "RANK",  
  
  
 
  
  
DENSE_RANK() OVER ( Partition By DEPARTMENTID Order By SALARY Desc ) "DENSE_RANK",  
  
  
 
  
  
ROW_NUMBER() OVER( Partition By DEPARTMENTID Order By SALARY Desc) "ROW_NUMBER"  
  
  
 
  
  
From EMPLOYEEINFO 
 
 

The result is as follows:

If a blank salary record is inserted, execute the preceding statement and the result is as follows:

It will be found that the null value is in the first place, which is obviously not the expected result. The solution is to add NULLS Last after the Over clause Order:

 
 

  
  
Select EMPLOYEENAME,SALARY,  
  
  
 
  
  
RANK() OVER (Order By SALARY Desc  Nulls Last)  "RANK",  
  
  
 
  
  
DENSE_RANK() OVER (Order By SALARY Desc Nulls Last) "DENSE_RANK",  
  
  
 
  
  
ROW_NUMBER() OVER(Order By SALARY Desc Nulls Last ) "ROW_NUMBER"  
  
  
 
  
  
From EMPLOYEEINFO 
 
 

The result is as follows:

This article introduces you here. If you want to know