Team Fly 

Page 347

NOTE
Some of the functions in this section are based on complex statistical calculations. Don't be worried if you are unfamiliar with these concepts. It is more important for you to know that these functions exist than it is to understand the theory behind them.

Ranking Functions

Ranking functions provide the ability to rank a row of a query result relative to the other rows in the result set. Common examples of uses for these functions include identifying the top ten selling products for a period, or classifying or grouping a salesperson's commissions into one of four buckets. The ranking functions included in Oracle Database 10g are

Image rank

Image dense_rank

Image cume_dist

Image percent_rank

Image ntile

Image row_number

rank and dense_rank Functions The simplest ranking functions are rank and dense_rank. These functions are very similar and determine the ordinal position of each row within the query result set. The difference between these two functions is that rank will leave a gap in the sequence when there is a tie for position, whereas dense_rank does not leave a gap. The results of the following listing illustrate the difference between the two.

select prod_id,
       sum(quantity_sold),
       rank () over (order by sum(quantity_sold) desc) as rank,
       dense_rank () over (order by sum(quantity_sold) desc) as dense_rank
from sales
where to_char(time_id, 'yyyy-mm') = '2001-06'
group by prod_id;

   PROD_ID SUM(QUANTITY_SOLD)       RANK DENSE_RANK
---------- ------------------ ---------- ----------
        24                762          1          1
        30                627          2          2
Team Fly 
0366