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


advertisement
FETCH FIRST for Top-N
DB2's method of performing a Top-N query is the FETCH FIRST clause. You can append these variations to a regular SELECT query:

  • FETCH FIRST ROW ONLY
  • FETCH FIRST 1 ROW ONLY
  • FETCH FIRST integer ROWS ONLY
Interestingly, you can also use

  • FETCH FIRST 1 ROWS ONLY
  • FETCH FIRST 5 ROW ONLY
They aren't as nice grammatically, but they make it easier to generate queries automatically—you don't have to worry about whether to say ROW or ROWS.

For demonstration, I'll create a table called MYSALES, which can be generated by running Listing 1.

Now, we can ask for a single record as follows:

Listing 2. Return a Single Row

SELECT * FROM MYSALES FETCH FIRST ROW ONLY CLIENT MONTHEND SALEVOL DEVX 03/31/1998 100

We have retrieved one row, but there's no way to know ahead of time which row it will be.(HINT: This does give us a handy way to remind ourselves what fields are in a table, with a row of sample data as a bonus!)

To find our top two clients, use the code in Listing 3.

Listing 3: Show Top Two Clients

SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL FROM MYSALES GROUP BY CLIENT ORDER BY SUM(SALEVOL) DESC FETCH FIRST 2 ROWS ONLY CLIENT TOTALVOL DEVX 5785 EGGHEAD 5341



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