Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Mastering Top-N and OLAP Queries in DB2-3 : Page 3


advertisement
Ranking and Numbering
DB2 supports ranking and numbering in much the same way that Oracle does (see "Performing Top-N Queries in Oracle").

The available functions are:

  • ROW_NUMBER(), which simply numbers the returned rows sequentially
  • RANK(), which ranks the results, but, in the case of a tie, gives the same number to each and leaves a gap to compensate
  • DENSE_RANK() operates the same as RANK() but doesn't leave any gaps.
The difference in these three methods can be seen in Listing 4.

Listing 4. 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 CLIENT MONTHEND SALEVOL RN RANK DENSE CYRIX 11/30/1997 120 1 1 1 BIG BLUE 11/30/1997 106 2 2 2 EGGHEAD 11/30/1997 106 3 2 2 DEVX 11/30/1997 80 4 4 3 FIGTREE 11/30/1997 62 5 5 4 ACME 11/30/1997 20 6 6 5

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

Listing 5. 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 as shown in Listing 6.

Listing 6. 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



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date