SQL limit not working: what is the right syntax?

Question

I have a simple question, and I am sorry in advance if this is too basic. I am connecting to a remote database using

import pyodbc
import pandas as pd
import numpy as np

cnxn = pyodbc.connect('DSN=MYDSN')

and I am able to pull some data using

sql = "SELECT * FROM MASTER.PRICES"
dataframe = pd.read_sql(sql, cnxn)

However, using the query

sql = "SELECT * FROM MASTER.PRICES LIMIT 10"
sql = "SELECT * FROM MASTER.PRICES where ROWNUM <= 10"

give an error such as

Unable to parse query text: Incorrect syntax near "SELECT", found "10".

for the first query.

My questions are:

without further information about the database, how can I know what is the right syntax for a LIMIT statement?
How can I even know what kind of database I am accessing?

Thanks!

**How can I even know what kind of database I am accessing** - How are you connecting - you must be providing some kind of connection string or some parameter to the driver right? — Gurwinder Singh, Feb 10 '17 at 13:44
@GurV I am using `cnxn = pyodbc.connect('DSN=MYDSN')` and the drivers were installed by someone. that s all I know! — ℕʘʘḆḽḘ, Feb 10 '17 at 13:50
@ChrisTravers I confess my noobiness here. Can you please tell me how to do that? — ℕʘʘḆḽḘ, Feb 10 '17 at 13:54
You will probably get better answers if you ask about inspecting the dsn to find the db driver in another question tagged python. Best not to mix questions too much. — Chris Travers, Feb 10 '17 at 13:55
OK I can create another question, but I just need to understand whyknowing the db driver can tell me about my problem dont you think — ℕʘʘḆḽḘ, Feb 10 '17 at 13:57
btw using the query `sql = "SELECT * FROM MASTER.PRICES ORDER BY BID_PRICE LIMIT 10"` gives the similar error `DatabaseError: Execution failed on sql 'SELECT * FROM MASTER.PRICES ORDER BY BID_PRICE LIMIT 10': ('HY000', '[HY000] Unable to parse query text: Incorrect syntax near "ORDER", found "LIMIT". On line 1, column 72.\n [parser-2904201]\nCause: Incorrect syntax near "ORDER", found "LIMIT" (1000) (SQLExecDirectW)')` — ℕʘʘḆḽḘ, Feb 10 '17 at 13:59
In case your db happens to be DB2, then read this: http://stackoverflow.com/questions/3885193/equivalent-of-limit-for-db2 — Arvo, Feb 10 '17 at 14:32

score 1 · Accepted Answer · answered Feb 10 '17 at 13:50

I think you are looking in the wrong place.

Yes, it is best practice to use ORDER BY before LIMIT but not strictly needed on MySQL. However that does not explain the syntax errors.

The syntax error suggests that something is going wrong in a different direction. Note tat it is near SELECT and not near LIMIT which makes me wonder if you have assembled your query in a way you are not telling us.

Maybe you copied and pasted things in and got a funny unicode character instead of a whitespace? But I don't see anything wrong with the way you are using LIMIT from a purely syntactic way and I bet if you type it in a mysql client you get no syntax errors.

SQL limit not working: what is the right syntax?

1 Answers1