Mastering Top-N and OLAP Queries in DB2

ith the help of today's Web integration and GUI tools, it's now much easier to build reports without any programming. SQL provides GROUP BY and ORDER BY clauses, to collate and sort otherwise unordered data. But sometimes we want more flexibility. Consider the following report requests:

• List my top two clients, according to sales totals
• List the next three clients
• Rank all clients by sales volume, but list them alphabetically
• Rank all clients by sales volume on a per-month basis
• Show each client's year-to-date and all-time cumulative totals
• A rolling three-month average, from one before to one after, and the same from the previous year

Traditionally the extra processing for these kinds of queries was done in application code. Using newer SQL constructs, we can move the processing closer to the data itself, both for efficient processing and simpler data access.

How can I rank my results in DB2 and retrieve only the data I want?



Use DB2's OLAP functions to refine the data; Use FETCH FIRST to minimize the results.