return rows 'x' through 'x' in db2

[bpimmerman]

Hey, i'm trying to do a sql statement where i only return certain rows, but i need to be able to go through(page one, page two, etc). Any ideas? I think i'm going to try and set up a cursor, but i'm not sure if my frontend will have access. Thanks.

 

[Mycroft999]

Are we talking tables with multi-value/multi-subvalue field structures?

 

[Cardboard Hammer]

You can use TOP and LEFT (or RIGHT, depending on how you write it) JOIN to get what you want. Note that the row source MUST have at least one candidate key for it to work right all the time. Also note that if you use a stored procedure, the number used for TOP can't be variable, unless you build the query in a string and use EXEC on it. That being said...

To get rows 101 - 110:

SELECT L.col FROM
(SELECT TOP 110 pk, col, whatever FROM A ORDER BY whatever) AS L
LEFT JOIN
(SELECT TOP 100 pk, col, whatever FROM A ORDER BY whatever) AS R
ON L.pk = R.pk
WHERE R.pk IS NULL
ORDER BY L.whatever

EDIT: Oh, crap... You said DB2... In which case, I'm not sure whether the code shown will do the trick or not. It probably will, but there may be better ways to do it with DB2... can't really say, as I've never used that DBMS.