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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

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


advertisement

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date