'limit' clause in Oracle SQL "SQL command not properly ended" [duplicate]

Question

I know that questions related to 'limit' have been asked before here, and I have already referred to them. My question is somewhat different.

Here's my query:

select id,somecol from sometable where someval=2 order by id desc limit 3

I'm getting an error saying 'SQL command not properly ended'. How do I resolve this? If you need additional information, feel free to tell me so.

See the answers here. Is this Oracle 12c? If so, replace "limit 3" with "FETCH FIRST 3 ROWS ONLY". If not, I think the standard Oracle-y thing to do is "SELECT * FROM (select id,somecol from sometable where someval=2 order by id desc) WHERE rownum <= 3" — EdmCoff
– EdmCoff, Commented Feb 14, 2018 at 6:37

Jayanth · Accepted Answer · 2018-02-14 06:59:30Z

Generally, we use LIMIT in MYSQL database and Rownum in Oracle.

MySQL Syntax:

SELECT column_name(s) FROM table_name WHERE condition LIMIT number;

Oracle Syntax:

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

References:

https://www.w3schools.com/sql/sql_top.asp

'select id,somecol from sometable where someval=2 order by id desc where rownum<=3' should work, right?

Kaushik Nayak · Accepted Answer · 2018-02-14 06:37:05Z

If you are running Oracle 12c, you could use FETCH FIRST n ROWS ONLY:

SELECT id, somecol FROM sometable WHERE someval = 2 ORDER BY id DESC FETCH FIRST 3 ROWS ONLY;

2 Answers 2