How to do SQL select top N ... in AS400
Wouldn't it be easier to limit the result instead? Bellow is in order by date and I take the top result
SELECT banana_equipment_id
FROM new_banana_equipment
WHERE banana_code=0000001
ORDER BY banana_date DESC
LIMIT 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.
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.