oracle--(rank) ranking function

Content from: Oracle®database SQL Language Reference 11g Release 2 (11.2) e41084-03.

The Empolyees table is from the HR scenario.

The rank function calculates the position of a value in a particular sort (using the aggregation syntax) and calculates the rank by the value of the specified column (using statistical syntax).

train map for aggregation syntax :

The following statement calculates the salary $2215 and the employee with a commission of 0.5% ranking in the results of the query.

SELECT RANK (.  GROUP(ORDER  by salary, commission_pct) ' Rank ' from employees_t;

Query Result:

      Rank----------         1

ORDER BY: A sort field is specified, and the query collection is only sorted by this clause, so we can get the position of the value in the sorted collection.

RANK: The function must have the same number of arguments as the order by field.

DESC, ASC: If you do not specify descending (DESC), the collection is arranged in ascending order (ASC), so the rank can be sorted either in ascending or descending order.

NULLS First, NULLS last: Specifies whether null values are placed at the beginning or at the end of the arrangement, so this affects the value rank.

Train chart for statistical grammar :

This syntax is suitable for querying the top or top ranked records

The following statement can query the rank of all employees in department 60, ranking according to the salary from low to High:

SELECTover byORDER by salary) RANK from WHERE=order by RANK, last_name;

Query Result:

department_id last_name SALARY RANK------------- ------------------------- ---------- ----------            -Lorentz4200.00          1            -Austin4800.00          2            -Pataballa4800.00          2            -Ernst6000.00          4            -Hunold9000.00          5

RANK: You do not need to specify parameters.

Over: Specifies the Rank object.
PARTITION by: You can partition the rank, specify the partition field.

ORDER BY: The same as the order by usage in the aggregation syntax.

In the example above, the employees in each section were ranked, showing only department 60. Note that the rank number may not be contiguous, and if there are two second-ranked records, their rank number is 2, followed by a record with a rank number of 4.

oracle--(rank) ranking function