DB2 - Ranking data by timeframe

Question

I am trying to write a report (DB2 9.5 on Solaris) to do the following:

I have a set of data, let's say it's an order table. I want to run a report which will give me, for each month, the number of orders per customer, and their "rank" that month. The rank would be based on the number of orders. I was playing around with the RANK() OVER clauses, but I can't seem to get it to give me a rank per month (or other "group by"). If there are 100 customers and 12 months of data, i would expect 1200 rows in the report, 100 per month, each with a rank between 1 and 100. Let me know if more detail would be helpful. Thanks in advance.

Answer 1

the solution is to use the PARTITION BY clause.

for example, see page 5 here: http://cmsaville.ca/documents/MiscDocs/TopNQueries.pdf