Search code examples
db2limitibm-midrange

Equivalent of LIMIT for DB2

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)

Solution

  • Developed this method:

    You NEED a table that has an unique value that can be ordered.

    If you want rows 10,000 to 25,000 and your Table has 40,000 rows, first you need to get the starting point and total rows:

    int start = 40000 - 10000;

    int total = 25000 - 10000;

    And then pass these by code to the query:

    SELECT * FROM 
(SELECT * FROM schema.mytable 
ORDER BY userId DESC fetch first {start} rows only ) AS mini 
ORDER BY mini.userId ASC fetch first {total} rows only