Browse DevX
Sign up for e-mail newsletters from DevX


Mastering Top-N and OLAP Queries in DB2

DB2 Pro Greg Nash teaches you to use DB2's OLAP functions to refine the data, then use FETCH FIRST to minimize the results.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






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



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