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-4 : Page 4


advertisement
Partitioning—Ranking within Groups
We have seen ROW_NUMBER and RANK operating over an ORDER clause with one or more columns. The ORDER BY must be specified for RANK() and DENSE_RANK(), but is optional for ROW_NUMBER(). That is, selecting

ROW_NUMBER() OVER()

will number the rows in an arbitrary fashion.

The other clause that may be used with RANK, DENSE_RANK, and ROW_NUMBER is a partitioning clause. This allows you to perform the function over subsets of data.

The query in Listing 7 will obtain the ranking of clients for each month in the past year.

Listing 7. Ranking in partitions

SELECT CLIENT, MONTHEND, SALEVOL, RANK() OVER (PARTITION BY MONTHEND ORDER BY SALEVOL DESC) AS MONTHRANK FROM MYSALES WHERE MONTHEND > CURRENT DATE - 1 YEAR ORDER BY MONTHEND

As with the ORDER clause, it is possible to specify multiple PARTITION columns, separated by commas.


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