Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E26088-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

DENSE_RANK

Aggregate Syntax

dense_rank_aggregate::=

Description of dense_rank_aggregate.gif follows
Description of the illustration dense_rank_aggregate.gif

Analytic Syntax

dense_rank_analytic::=

Description of dense_rank_analytic.gif follows
Description of the illustration dense_rank_analytic.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

This function accepts as arguments any numeric data type and returns NUMBER.

Aggregate Example

The following example computes the ranking of a hypothetical employee with the salary $15,500 and a commission of 5% in the sample table oe.employees:

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

Dense Rank
----------
         3

Analytic Example

The following statement selects the department name, employee name, and salary of all employees who work in the human resources or purchasing department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for RANK.

SELECT d.department_name, e.last_name, e.salary, DENSE_RANK() 
  OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drank
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
    AND d.department_id IN ('30', '40')
  ORDER BY e.last_name, e.salary, d.department_name, drank;

DEPARTMENT_NAME                LAST_NAME                     SALARY      DRANK
c------------------------------ ------------------------- ---------- ----------
Purchasing                     Baida                           2900          4
Purchasing                     Colmenares                      2500          1
Purchasing                     Himuro                          2600          2
Purchasing                     Khoo                            3100          5
Human Resources                Mavris                          6500          1
Purchasing                     Raphaely                       11000          6
Purchasing                     Tobias                          2800          3