Selecting first few rows in DB2

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.

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

Listing 1 - 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.

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!

Listing 2 - 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 |

comments powered by Disqus