Oracle RANK
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose 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.

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.

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.
 
Aggregation
Single Column RANK as Aggregation Function RANK(<rank expression>) WITHIN GROUP
(ORDER BY <expression> <ASC|DESC> NULLS <FIRST|LAST>);
conn hr/hr@pdbdev

-- 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 commission of 3.6%

conn hr/hr@pdbdev

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 commission of 3.6%

conn hr/hr@pdbdev

SELECT RANK(.36, 15500) WITHIN GROUP
(ORDER BY commission_pct NULLS FIRST, salary) RANK
FROM employees;
 
Analytic
With Order By Clause RANK() OVER (<order by clause>);
-- find the employee with the 2nd highest salary

conn hr/hr@pdbdev

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;
With Partition By Clause RANK() OVER (<query partition clause> <order by clause>);
conn hr/hr@pdbdev

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;

Related Topics
Analytic Functions
Numeric Functions
Oracle Built-in Functions
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved