SQL command not properly ended when using LIMIT

Question

I am executing the below query in SQL Developer.

SELECT * FROM Person where person_name='rahul' order by created_time desc limit 10;

When I execute it, SQL Developer gives me below error.

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

I used the below link for reference.

https://www.techonthenet.com/sql/select_limit.php

I already tried

SELECT * FROM Person where person_name='rahul' order by created_time desc OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 1 Column: 75

Note that OFFSET is not treated as keyword.

Any change if you remove the ; ? – Mihai Dec 02 '16 at 10:01 — Mihai, Dec 02 '16 at 10:01

score 15 · Answer 1 · edited May 23 '17 at 12:01

Yes, that' cause Oracle don't have or support limit clause and thus you are getting ORA-00933 error. Rather, use FETCH .. OFFSET construct like

SELECT * FROM Person 
where person_name='rahul' 
order by created_time desc 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

There are lot many similar question in StackOverflow. Should have tried searching the same. Example this one: How do I limit the number of rows returned by an Oracle query after ordering?

score 3 · Accepted Answer · answered Dec 05 '16 at 09:01

3

I have resolved the issue by using the below query.

SELECT * FROM Person where person_name='rahul' and rownum between 1 and 2 order by created_time desc;

answered Dec 05 '16 at 09:01

Tushar Banne

870
3
12
33

score 2 · Answer 3 · answered Jun 22 '18 at 16:51

2

If you get

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

It is probably because you aren't running Oracle 12. In that case, there are some workarounds, all involving subqueries and most sloppy. I used

select * from 
    ( select column_name, ROWNUM rnum from 
        ( select * from table_name) 
    where ROWNUM <= max_row )
where rnum  >= min_row order by column_name;

answered Jun 22 '18 at 16:51

cyarbrough

21
1

Note that becuase the `order by` is not in the inner most subquery it will only order the outermost subgroup. Basically if you have 1000 records, and you fetch 100-125, it will get a random set of 25 records and sort that. I think you want the `order by` after the `select * from table_name` part. – Veda Nov 21 '19 at 09:18

SQL command not properly ended when using LIMIT

3 Answers3

Linked