'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.

See the answers [here](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering). 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, Feb 14 '18 at 06:37

score 9 · Accepted Answer · answered Feb 14 '18 at 06:59

9

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

answered Feb 14 '18 at 06:59

Jayanth

'select id,somecol from sometable where someval=2 order by id desc where rownum<=3' should work, right? – Pratyush Panshikar Feb 14 '18 at 15:45
okay, this worked! thank you! – Pratyush Panshikar Feb 14 '18 at 15:46

score 3 · Answer 2 · answered Feb 14 '18 at 06:37

3

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;

answered Feb 14 '18 at 06:37

Kaushik Nayak

2 Answers2