Order By and Limit gives error

Question

I am running the query below:

Select ID From Players order by ID limit 3;

It gives the error

Command Not Properly Ended.

Also the queries below:

SQL> Select ID From Players order by ID limit 3;
     Select ID From Players order by ID limit 3
                               *

Gives similar error:

ERROR at line 1: ORA-00933: SQL command not properly ended

How can I fix it?

Answer 1

Oracle don't have or support limit clause and thus you are getting error.

Use this query:

Select ID From Players order by ID
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

OR

Select ID From Players order by ID
where rownum between 1 and 3 order by ID desc;

Cause: The SQL statement ends with an inappropriate clause.

See this post for more info.

Answer 2

Oracle doesn't support limit. Oracle 12c supports fetch first <n> rows only. In earlier versions you can use a subquery and rownum:

select *
from (select ID 
      from Players
      order by ID desc
     ) p
where rownum <= 3;