0

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
psychorama
  • 193
  • 3
  • 15
  • 1
    You should place `ORDER BY` at last, try this `WHERE rownum>=100 AND rownum<=200 ORDER BY USER_ID` – Susang Jan 24 '17 at 07:11
  • And what should the rownum condition do? Have a look at [this](http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) – Aleksej Jan 24 '17 at 07:22
  • "*I think it's the proper syntax*" - if it was, you wouldn't get a syntax error – a_horse_with_no_name Jan 24 '17 at 08:17
  • 1
    What query did you try? and what error are you having? Please post the query you took from that answer and the error it's giving to you – Aleksej Jan 24 '17 at 08:21
  • @Aleksej I saw the other answer, however, the query made from that doesn't work either, and throws the same error : SELECT * from TESTTABLE1 ORDER BY USER_ID OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY – psychorama Jan 24 '17 at 08:21
  • The error is same. ORA-00933: SQL command not properly ended – psychorama Jan 24 '17 at 08:22
  • Which Oracle version? That answer is for Oracle 12c, and it's cleary written in the answer, while other answers will work even for earlier versions – Aleksej Jan 24 '17 at 08:23

2 Answers2

0

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;
bin he
  • 177
  • 5
  • 1
    ROWNUM and ORDER BY don't play together the way you'd like them to. Also, won't this always return the first 100 rows? – APC Jan 24 '17 at 08:20
  • I use 'desc' to get the second 100 rows. – bin he Jan 24 '17 at 08:51
  • So you reverse the order of the returned result set. Really You need another outer query to ORDER BY USER_ID ASC to get the results back in the correct order. Which is a lot of sorts. Isn't it just easier to use something other than ROWNUM? – APC Jan 24 '17 at 12:34
0

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.

APC
  • 137,061
  • 19
  • 153
  • 266