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
– Mihai

2016-12-02 10:01:21 +00:00
Commented Dec 2, 2016 at 10:01 — Mihai
– Mihai, Commented Dec 2, 2016 at 10:01
No. Still the same.

Tushar Banne
– Tushar Banne

2016-12-02 10:09:12 +00:00
Commented Dec 2, 2016 at 10:09 — Tushar Banne
– Tushar Banne, Commented Dec 2, 2016 at 10:09

Chris Harrison · Accepted Answer · 2024-08-20 10:00:44Z

32

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;

edited Aug 20, 2024 at 10:00

Chris Harrison

5,9075 gold badges32 silver badges36 bronze badges

answered Dec 2, 2016 at 9:35

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tushar Banne · Accepted Answer · 2016-12-05 09:01:07Z

5

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 5, 2016 at 9:01

Tushar Banne

1,7655 gold badges21 silver badges40 bronze badges

Comments

cyarbrough · Accepted Answer · 2018-06-22 16:51:44Z

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, 2018 at 16:51

cyarbrough

811 silver badge2 bronze badges

1 Comment

Veda Over a year ago

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.

Collectives™ on Stack Overflow

SQL command not properly ended when using LIMIT

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related