'limit' clause in Oracle SQL "SQL command not properly ended"

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.

Solution 1

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

Solution 2

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;

Comments

[Pratyush Panshikar]

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

[Pratyush Panshikar]

okay, this worked! thank you!