Problem with DB2 Over clause

Question

I'm trying to do pagination with a very old version of DB2 and the only way I could figure out selecting a range of rows was to use the OVER command.

This query provide's the correct results (the results that I want to paginate over).

select MIN(REFID) as REFID, REFGROUPID from ARMS_REFERRAL where REFERRAL_ID<>'Draft' and REFERRAL_ID not like 'Demo%' group by REFGROUPID order by REFID desc

Results:

   REFID       REFGROUPID
    302         242
    301         241
    281         221
    261         201
    225         142
    221         161
    ...         ...

SELECT * FROM ( SELECT row_number() OVER () AS rid, MIN(REFID) AS REFID, REFGROUPID FROM arms_referral where REFERRAL_ID<>'Draft' and REFERRAL_ID not like 'Demo%'  group by REFGROUPID order by REFID desc ) AS t WHERE t.rid BETWEEN 1 and 5

Results:

REFID       REFGROUPID
26          12
22          11
14          8
11          7
6           4

As you can see, it does select the first five rows, but it's obviously not selecting the latest.

If I add a Order By clause to the OVER() it gets closer, but still not totally correct.

SELECT * FROM ( SELECT row_number() OVER (ORDER BY REFGROUPID desc) AS rid, MIN(REFID) AS REFID, REFGROUPID FROM arms_referral where REFERRAL_ID<>'Draft' and REFERRAL_ID not like 'Demo%'  group by REFGROUPID order by REFID desc ) AS t WHERE t.rid BETWEEN 1 and 5

REFID       REFGROUPID
302         242
301         241
281         221
261         201
221         161

It's really close but the 5th result isn't correct (actually the 6th result).

How do I make this query correct so it can group by a REFGROUPID and then order by the REFID?

Answer 1

what if you put the order by on the outside of the parens?

SELECT * 
FROM ( SELECT row_number() OVER (ORDER BY REFGROUPID desc) AS rid, 
MIN(REFID) AS REFID, REFGROUPID FROM arms_referral where REFERRAL_ID<>'Draft' 
and REFERRAL_ID not like 'Demo%'  group by REFGROUPID ) AS t 
WHERE t.rid BETWEEN 1 and 5 order by REFID desc