background image
<< Determining Statistical Information | Using Conditional Functions >>
<< Determining Statistical Information | Using Conditional Functions >>

Determining RANK and PERCENT_RANK

Retrieving Data with Queries
Querying and Manipulating Data 2-25
You can also examine groups using the WITHIN GROUP function.
Example 2­41 Determining RANK and PERCENT_RANK
SELECT RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank",
ROUND(100 * (PERCENT_RANK(3000)
WITHIN GROUP (ORDER BY salary DESC)), 0) "Percentile"
FROM employees
WHERE job_id LIKE '%CLERK';
The results of the query appear.
Rank Percentile
---------- ----------
20 42
Your result shows that a salary of $3,000 is the 20
th
highest, and that it is in the 42
nd
percentile among all employees who have a '
CLERK
' designation.
The
DENSE_RANK
function works much like the
RANK
function, but the identical
values receive the same rank, and there are no gaps in the ranking. In
Example 2­42
,
you will determine the
DENSE_RANK
of $3,000 over the subset of all employees who
have a '
CLERK
' designation in the
job_id
.
Example 2­42 Determining DENSE_RANK:
SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank"
FROM employees
WHERE job_id LIKE '%CLERK';
The results of the query appear.
Rank
----------
12
Your result shows that a salary of $3,000 is the 12
th
highest using the
DESNE_RANK
function. Contrast it with the 20
th
rank obtained in the previous example that used the
RANK
function.
Using NULL Value Functions
To work with
NULL
values, Oracle Database supplies two functions.
NVL
substitutes a
specified value if a
NULL
is encountered, and
NVL2
specifies two possible expressions
that could be evaluated (one if none of its component variables is
NULL
, and another
one if at least one variable is
NULL
).
In
Example 2­43
, you will use the
NVL
and
NVL2
functions to determine what the
whole annual compensation would be for each employee, if they were involved in a
$300,000 sale. Note that the commission rate is a multiplier on sales volume, not on
base salary. Note also that the
WHERE
clause limits the result set to managers.
Example 2­43 Using the NVL and NVL2 Functions
SELECT first_name || ' ' || last_name "Name",
NVL((commission_pct * 100), 0) "Comm Rate",
NVL2(commission_pct,
See Also:
Oracle Database SQL Language Reference for aggregate functions