| General |
| Calculates the rank of a value in a group
of values. Rows with equal values for the ranking criteria receive the same rank. Oracle
then adds the number of tied rows to the tied rank to calculate the next rank. Therefore,
the ranks may not be consecutive numbers. |
| |
| Aggregation |
| 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. |
Single Column RANK as Aggregation
Function |
RANK(<rank expression>) WITHIN GROUP
(ORDER BY <expression> <ASC|DESC> NULLS <FIRST|LAST>) |
conn hr/hr
-- the following query returns the rank for a $15,500 salary
SELECT RANK(15500) WITHIN GROUP
(ORDER BY salary DESC NULLS LAST) SAL_RANK
FROM employees; |
Multiple Column RANK as Aggregation
Function |
RANK(<rank expression>) WITHIN GROUP
(ORDER BY <expression> <ASC|DESC> NULLS <FIRST|LAST>) |
The following query returns the rank
of a hypothetical employee with a salary of $15,500 and a commission of 3.6%
conn hr/hr
SELECT RANK(.36, 15500) WITHIN GROUP
(ORDER BY commission_pct, salary) RANK
FROM employees; |
Multiple Column RANK as Aggregation
Function |
RANK(<rank expression>) WITHIN GROUP
(ORDER BY <expression> <ASC|DESC> NULLS <FIRST|LAST>) |
The following query returns the rank
of a hypothetical employee with a salary of $15,500 and a commission of 3.6%
conn hr/hr
SELECT RANK(.36, 15500) WITHIN GROUP
(ORDER BY commission_pct NULLS FIRST, salary) RANK
FROM employees; |
| |
| 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. |
RANK Analytic Function |
RANK() OVER (<order by clause>) |
-- find the employee with the
2nd
highest salary
conn hr/hr
SELECT *
FROM (
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) EMPRANK
FROM employees)
WHERE emprank = 2;
-- verify result
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) EMPRANK
FROM employees; |
-- find the 20 largest tables
in a tablespace
conn / as sysdba
col segment_name format a30
SELECT * FROM (
SELECT segment_name, blocks, RANK() OVER(ORDER BY blocks DESC) SZ
FROM dba_segments
WHERE segment_type = 'TABLE'
AND TABLESPACE_NAME = 'UWDATA')
WHERE SZ < 21; |
RANK Analytic Function |
RANK() OVER (<query partition clause> <order by clause>)
|
conn hr/hr
SELECT department_id,last_name,salary,commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) RANK
FROM employees;
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;
|
