Problem with DB2 Over clause
- by silent1mezzo
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?
