How to do SQL select top N ... in AS400

Question

How do you perform a

Select top N * from as400table 

type query against an as400/db2 database

Answer 1

Select col1,col2 from  as400table where col1='filter' order by col1 fetch first N row only 

Remember to set an ORDER BY clause, because DB2 does not guarantee that the rows returned by FETCH FIRST N ROW ONLY are always the same N.

Answer 2

Strictly, there is no equivalent of TOP N in DB2.

SELECT 1 FROM sysibm.sysdummy1     WHERE EXISTS (SELECT 2 FROM sysibm.sysdummy1) FETCH FIRST ROW ONLY 

compiles and runs, but

SELECT 1 FROM sysibm.sysdummy1     WHERE EXISTS (SELECT 2 FROM sysibm.sysdummy1 FETCH FIRST ROW ONLY) 

will not compile.

TOP N and FETCH FIRST N are not the same. You can only use FETCH FIRST once per query, whereas TOP N can be used in any sub-select.

You can use a window function in a sub-query in order to simulate TOP N:

select * from (     select id, row_number()     over (order by id) as rn     from testsch.testtbl ) as r where r.rn < 100 -- This is N rows you are looking for 

This will return exactly 99 rows. I tried that in iSeries 7 and it worked.