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?

Where in [the Oracle manual](https://docs.oracle.com/database/121/SQLRF/toc.htm) did you find `LIMIT`? — , Feb 08 '18 at 20:44

Hasan Fathi · Accepted Answer · 2018-02-08T18:47:29.500

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.

edited Feb 08 '18 at 18:47

answered Feb 08 '18 at 18:17

Hasan Fathi

score 0 · Answer 2 · answered Feb 08 '18 at 18:35

0

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;

answered Feb 08 '18 at 18:35

Gordon Linoff

2 Answers2