Use of Rank, Dense_Rank, and Row_Number in Oracle

Before using the oracle database to create an employee Table Code, when the order by field ASC (DESC) is used to sort the dataset, there is no way to directly obtain the ranking. When the result set must be traversed, custom sorting number. I have recently seen a good way to share it here .. This section describes three different sorting methods and completes www.2cto.com 1. Use the Rank function to return a unique value, unless the same data is encountered, in this case, the ranking of all the same data is the same, and the ranking of the last same record and next different record is empty. 2. A unique value is returned using the Dense_Rank keyword Dense_rank function, unless the same data is met, the ranking of all the same data is the same. 3. Use the Row_Number keyword Row_number function to return a unique value. When the same data is encountered, the ranking increases sequentially according to the record sequence in the record set. The following example shows how to Create an employee Table at www.2cto.com: Java CODE Create Table EmployeeInfo (CODE Number (3) Not Null, EmployeeName varchar2 (15), using mentid Number (3 ), salary NUMBER (), Constraint PK_EmployeeInfo Primary Key (CODE); Use the Rank, Dense_Rank, Row_Number keyword to query the query statement CODE www.2cto.com 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) the result of the "ROW_NUMBER" From EMPLOYEEINFO query section is: www.2cto.com


We can see that the sorting results are all sorted by the specified fields in Over (). You can see the sorting results. Of course, if we want to group data, you only need to add Partition by groupField in the over clause, for example, grouping Order 1 code Select Partition mentid, EMPLOYEENAME, SALARY, RANK () OVER (Partition By Partition mentid Order By SALARY Desc) "RANK ", DENSE_RANK () OVER (Partition By Partition mentid Order By SALARY Desc) "DENSE_RANK", ROW_NUMBER () OVER (Partition By Partition mentid Order By SALARY Desc) "ROW_NUMBER" From employee info result: www.2cto.com


Secondly, if a field with null sorting fields is inserted in the first row by default, we can specify Nulls Last to display the records with null sorting fields at the end: the query statement is: www.2cto.com. The query null records are displayed in the final code Select EMPLOYEENAME, SALARY, RANK () OVER (Order By SALARY DescNulls 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