How do you do LIMIT
in DB2 for iSeries?
I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000.
I know in SQL you write LIMIT 0,10000
at the end of the query for 0 to 10,000 and LIMIT 10000,10000
at the end of the query for 10000 to 20,000
So, how is this done in DB2? Whats the code and syntax? (full query example is appreciated)
Using FETCH FIRST [n] ROWS ONLY
:
SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
FROM EMP
ORDER BY SALARY DESC
FETCH FIRST 20 ROWS ONLY;
To get ranges, you'd have to use ROW_NUMBER()
(since v5r4) and use that within the WHERE
clause: (stolen from here: http://www.justskins.com/forums/db2-select-how-to-123209.html)
SELECT code, name, address
FROM (
SELECT row_number() OVER ( ORDER BY code ) AS rid, code, name, address
FROM contacts
WHERE name LIKE '%Bob%'
) AS t
WHERE t.rid BETWEEN 20 AND 25;