CODEWITHSUNDEEP
sqldb2
ianpolitavares
ianpolitavares

Reputation: 15

How can we get a set of rows starting with a specific row?

we have a database with the following config:

VALUE_1 DATE | VALUE_2 INT | VALUE_3 CHAR(4) | VALUE_4 CHAR(4) ...
  1. the key is made by these four values
  2. All these values can be repeated but
  3. ... Can't repeat the same row twice (only if one of the values is different)

The problem is: In our first SELECT we get the first 10 rows, after that, we want to get the first 10 rows beginning by the last one, ordered by VALUE_1 DATE | VALUE_2 INT | VALUE_3 CHAR(4) | VALUE_4 CHAR(4).

How can we get a set of rows starting with a specific row?

select -> ...
OUTPUT: v1, v2 ... v10

select -> where VALUE_1 > v10, VALUE_2 = (or >) value ...
OUTPUT: v11, v12 ... v20

Upvotes: 0

Views: 398

Answers (3)

Paul Vernon
Paul Vernon

Reputation: 3901

You can do this

CREATE TABLE T (VALUE_1 DATE, VALUE_2 INT, VALUE_3 CHAR(4), VALUE_4 CHAR(4) )

INSERT INTO T VALUES
  ( '2020-01-01', 1, 'A', 'A' )
, ( '2020-01-01', 2, 'A', 'B' )
, ( '2020-01-01', 2, 'A', 'C' )
 ,( '2020-01-01', 3, 'A', 'B' )

assuming the last row you got was the 2nd one (and there are no NULLs), this will get the next

SELECT * FROM T
WHERE   (VALUE_1, VALUE_2, VALUE_3, VALUE_4 ) > ( '2020-01-01', 2, 'A', 'B' )
ORDER BY VALUE_1, VALUE_2, VALUE_3, VALUE_4

See Table 3. Here https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000746.html

Upvotes: 1

Andrew
Andrew

Reputation: 8703

Based on the guess/assumption that you mean you can't use window functions (?), you can probably combine limit with offset:

Select
<some columns>
from
<your table>
order by
al1, val2, val3, val4
limit <how many rows you want to return>
offset <how many rows you want to skip>

So if you wanted, say, rows 101 through 110, you would use

limit 10
offset 100

Don't have access to a DB2 system at this point, but that should work, or be close.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think you can do this with window functions:

select t.*
from (select t.*, row_number() over (order by val1, val2, val3, val4) as seqnum
      from t
     ) t
order by (case when seqnum <= 10 then seqnum else 11 end), -- first 10 rows first
         seqnum desc  -- the rest in reverse order

Upvotes: 0

Related Questions