Ranking and Numbering for Records in DB2

DB2 supports ranking and numbering in much the same way that Oracle does.

The available functions are:

• ROW_NUMBER(), which simply numbers the returned rows sequentially </li>
• RANK(), which ranks the results, but, in the case of a tie, gives the same number to each and leaves a gap to compensate </li>
• DENSE_RANK() operates the same as RANK() but doesn’t leave any gaps.

Listing 1 - Ranking and Numbering Results

select 
client, MONTHEND, SALEVOL, 
ROW_NUMBER() over (order by SALEVOL desc) AS RN, 
RANK() over (order by SALEVOL desc) AS RANK, 
DENSE_RANK() over (order by SALEVOL desc) AS DENSE
from mysales
where MONTHEND=DATE('1997-11-30')
order by RN

The results need not be returned in rank order. We may wish to show each client’s rank while listing them alphabetically.

Listing 2 - Return Ranks in Any Order

select
CLIENT, SALEVOL,
RANK() over (order by SALEVOL desc) AS RANK
from mysales
where MONTHEND=DATE('1997-11-30')
order by CLIENT

One application of ROW_NUMBER is to select a numbered range of rows from the middle of your results. For example, you could retrieve the next three clients following the top two.

Listing 3 - Selecting Rows by Number

WITH ALLSALES AS 
(SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL,
ROW_NUMBER() OVER
(ORDER BY SUM(SALEVOL) DESC, CLIENT)
AS RN
FROM MYSALES 
GROUP BY CLIENT)

SELECT
CLIENT, TOTALVOL, RN
FROM ALLSALES
WHERE RN BETWEEN 3 AND 5
ORDER BY RN