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.
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?
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With