Equivalent of LIMIT for DB2

user_432539 0
Viewed 151852

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)

ibm-midrange db2 limit
10 Answers

Try this

SELECT * FROM
    (
        SELECT T.*, ROW_NUMBER() OVER() R FROM TABLE T
    )
    WHERE R BETWEEN 10000 AND 20000
user_27287660

The LIMIT clause allows you to limit the number of rows returned by the query. The LIMIT clause is an extension of the SELECT statement that has the following syntax:

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT n [OFFSET m];

In this syntax:

  • n is the number of rows to be returned.
  • m is the number of rows to skip before returning the n rows.

Another shorter version of LIMIT clause is as follows:

LIMIT m, n;

This syntax means skipping m rows and returning the next n rows from the result set.

A table may store rows in an unspecified order. If you don’t use the ORDER BY clause with the LIMIT clause, the returned rows are also unspecified. Therefore, it is a good practice to always use the ORDER BY clause with the LIMIT clause.

See Db2 LIMIT for more details.

user_131550460
Related