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
|CLIENT | SALEVOL |RANK|
|———|———-|—-| |ACME | 20 | 6|
|BIG BLUE | 106 | 2| |CYRIX | 120 | 1|
|DEVX | 80 | 4| |EGGHEAD | 106 | 2|
|FIGTREE | 62 | 5|
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
|CLIENT | TOTALVOL |RN| |———|————|–| |BIG BLUE | 4781 | 3| |FIGTREE | 3986 | 4| |ACME | 3044 | 5|