I'm getting error while running the following query. I think it's the proper syntax. Please help.
SELECT * from TESTTABLE1 ORDER BY USER_ID WHERE rownum>=100 AND rownum<=200
I'm getting error while running the following query. I think it's the proper syntax. Please help.
SELECT * from TESTTABLE1 ORDER BY USER_ID WHERE rownum>=100 AND rownum<=200
I'm not sure i'm following your meaning.if you want select 100th to 200th rows from the table ,the query statement may be helpful:
select *
from (select *
from (SELECT *
from TESTTABLE1
ORDER BY USER_ID)
where rownum <= 200
ORDER BY USER_ID desc)
where rownum <= 100;
Presumably a pagination issue. The hard part is getting a consistent sort order. Solutions with rownum
and order by
won't work because the stop is applied before the sort.
This solution has a with
clause sub-query to generate a sort key which will be reliable providing the columns in the order by
clause constitute unique combinations.
with pages as (
SELECT t1.*
, row_number() over (ORDER BY t1.USER_ID) as rn
from TESTTABLE1 t1)
select pages.user_id
from pages
WHERE pages.rn >=100 AND pages.rn <=200
Note you will need to specify columns in the main query's projection (to exclude the rn
), but that's good practice as select *
is a bug waiting to happen.