Creating a pagination with db2

I have read that DB2 doesn't support Limit and Offset. I also read that you have to uses ROW_NUMBER() and subqueries to get the desired result. If this is an SQL query:

$sql = "SELECT * FROM ITEMS LIMIT $offset, $rowsperpage";

where $offset is the offset and $rowsperpage is the amount of rows from database I want to be displayed on the page, what could be equivalent of this as a DB2 query.

Answers


Well, depending on what platform of DB2 you are using, you didn't read the full story. DB2 LUW has support for LIMIT and OFFSET, but you have to turn it on (don't forget to restart DB2 after setting the flag). If you want to use DB2 with ROW_NUMBER() as you asked for, you could write the query as follows:

SELECT * 
  FROM (SELECT ROW_NUMBER() OVER() AS rn, 
               items.* 
          FROM items)
  WHERE rn BETWEEN computelowerboundaryhere AND computeupperboundaryhere;

There is also an overview article describing the different ways of doing the LIMIT/OFFSET work in DB2.


DB2 for I also has support for LIMIT and OFFSET as of the latest technology refresh (7.1 TR11 and 7.2 TR3).


Need Your Help

Optionally including scripts in SQL Server Projects 2012

sql-server visual-studio-2012 project sql-server-data-tools sql-server-2012-datatools

I am building a SQL Publish Script that will be used to generate a database to our internal servers, and then used externally by our client.

How to change default caption color of group box in MFC programmatically?

visual-c++ mfc colors groupbox

Currently groupboxs in my application shows caption in blue color in Windows xp and black in windows 7. How to change default caption color of group box in MFC programmatically? so that it can be s...