Menu
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.
257
To correct this issue, simply go back to the end of the phrase and remove the ORDER BY clause. Be sure to go back to the line prior to the ORDER BY clause and re-insert the statement-ending semi-colon. Another case where the ORA-00933 can occur is when attempting to include an ORDER BY clause with a DELETE statement.
Oracle Database does not have the LIMIT clause.
You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e. You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.
Using the LOOP Statement END LOOP; With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. You use an EXIT statement to stop looping and prevent an infinite loop.
Oracle Database does not have the LIMIT clause. However, since 12c release, it provided a similar but more flexible clause named row limiting clause. By using the row limiting clause, you can rewrite the query that uses the LIMIT clause above as follows:
Introduction to Oracle FETCH clause Some RDBMS such as MySQL and PostgreSQL have the LIMIT clause that allows you to retrieve a portion of rows generated by a query. See the following products and inventories tables in the sample database. The following query uses the LIMIT clause to get the top 5 products with the highest inventory level:
If you’re getting an “ORA-00933 sql command not properly ended” on INSERT, then it could be because: You have a JOIN keyword (such as INNER JOIN, LEFT JOIN) in the query. You have an ORDER BY in the query.
The following query uses the row limiting clause with the WITH TIES option: Even though the query requested 10 rows, because it had the WITH TIES option, the query returned two more additional rows. Notice that these two additional rows have the same value in the quantity column as the row 10.
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
197
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
