The Oracle SQL aggregate and analytic function 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 datatype and returns NUMBER.
APPLIES TO: Oracle 9i, Oracle 10g, Oracle 11g
SYNTAX: DENSE_RANK() OVER (query_partition_clause order_by_clause)
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 employees:
SELECT
DENSE_RANK(15500, .05) WITHIN GROUP (ORDER BY emp.salary DESC,
emp.commission_pct) dense_rank
FROM
employees emp;
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
dep.department_id,
emp.last_name,
emp.salary,
DENSE_RANK() OVER (PARTITION BY emp.department_id ORDER BY emp.salary) as dense_rank
FROM
employees emp,
departments dep
WHERE
emp.department_id = dep.department_id
AND dep.department_id IN (’60′, ‘90′);
| department_id |
last_name |
salary |
dense_rank |
|
60 |
Lorentz |
4200 |
1 |
|
60 |
Pataballa |
4800 |
2 |
|
60 |
Austin |
4800 |
2 |
|
60 |
Ernst |
6000 |
3 |
|
60 |
Hunold |
9000 |
4 |
|
90 |
De Haan |
17000 |
1 |
|
90 |
Kochhar |
17000 |
1 |
|
90 |
King |
24000 |
2 |
OVERVIEW: Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a ‘window’ and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
