Oracle Database - SQL - RANK Function

Card Puncher Data Processing

About

rank in the Oracle database context.

Type of function

Analytic

As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

Syntax:

SELECT department_id, last_name, salary, commission_pct,
   RANK() OVER (PARTITION BY department_id
   ORDER BY salary DESC, commission_pct) "Rank"
FROM employees WHERE department_id = 80
ORDER BY department_id, last_name, salary, commission_pct, "Rank";

Aggregate

As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.

Syntax:

The following example calculates the rank of a hypothetical employee in the sample table hr.employees with a salary of 15,500 and a commission of 5%:

SELECT RANK(15500, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Rank"
   FROM employees;

      Rank
----------
       105

Similarly, the following query returns the rank for a 15,500 salary among the employee salaries:

SELECT RANK(15500) WITHIN GROUP 
   (ORDER BY salary DESC) "Rank of 15500" 
   FROM employees;

Rank of 15500
--------------
             4

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - SQL - Analytic Functions

Analytic functions in the Oracle Database Context An analytic function takes place after that the original data set is retrieved. The clause AVG CORR COVAR_POP COVAR_SAMP...
Analytic Function Process Order
SQL Function - Window Aggregate (Analytics function)

Windowing functions (known also as analytics) allow to compute: cumulative, moving, and aggregates. They are distinguished from ordinary SQL functions by the presence of an OVER clause. With...



Share this page:
Follow us:
Task Runner