SQL api endpoint "ORDER BY" + "WHERE" + "LIMIT" error in syntax

Developers
,
1

I try to port some of my code to the new sql endpoint, but i encounter an issue.

It seems that “ORDER BY” + “WHERE” + “LIMIT” clauses in combination are not allowed/supported.
At least i get an error message. Every one of those features work in isolation however:

# Works:
SELECT id, date FROM Checkresults LIMIT 10
 
# Works:
SELECT id, date FROM Checkresults ORDER BY date

# Works:
SELECT id, date FROM Checkresults WHERE date <= 1696634277

# Works:
SELECT id, date FROM Checkresults WHERE date <= 1696634277 LIMIT 10

# Does NOT work:
SELECT id, date FROM Checkresults ORDER BY date WHERE date <= 1696634277 LIMIT 10
# The Error: {"error":"SQLITE_ERROR: near \"WHERE\": syntax error"}

# Does NOT work:
SELECT id, date FROM Checkresults WHERE date <= 1696634277 LIMIT 10 ORDER BY date 
# The Error: {"error":"SQLITE_ERROR: near \"ORDER\": syntax error"}

# While this works again :):
SELECT id, date FROM Checkresults  WHERE date <= 1696634277  ORDER BY date

Any Tip? It seems like a bug, though.
Should i open a github issue?

2

Try putting WHERE before ORDER BY?

3

What do you mean?

this i tried:

SELECT id, date FROM Checkresults  WHERE date <= 1696634277  ORDER BY date
4

Right, and then LIMIT should stay at the end.

5

Then i get this:

SELECT id, date FROM Checkresults WHERE date <= 1696634277 LIMIT 10  ORDER BY date LIMIT 10
# {"error":"SQLITE_ERROR: near \"ORDER\": syntax error"}
6 
SELECT id, date FROM Checkresults WHERE date <= 1696634277 ORDER BY date LIMIT 10
7

scratch head yes this works. Thank you!

8

You can see the full available syntax here:
https://www.sqlite.org/lang_select.html