If you’re getting a SQLite error that reads ‘Parse error: near “LIMIT”‘, it could be that you’re trying to use the LIMIT clause when using the VALUES clause as a stand alone statement.
SQLite doesn’t allow us to apply the LIMIT clause against the VALUES statement.
However, there is a work around. Below is an example of how we can apply the LIMIT clause against the VALUES statement.
Example of Error
Here’s an example of code that produces the error:
VALUES
(1, 'Fred'),
(2, 'Wilma'),
(3, 'Barney')
LIMIT 2;Result:
Parse error: near "LIMIT": syntax error
(1, 'Fred'), (2, 'Wilma'), (3, 'Barney') LIMIT 2;
error here ---^
This error tells us that the error occurred at the LIMIT clause. Given the VALUES statement doesn’t accept the LIMIT clause, it’s no wonder we got the error.
Solution
Although we can’t apply the LIMIT clause against our VALUES statements, what we can do is place our VALUES statement into a subquery, and then apply the LIMIT clause to the outer query:
SELECT * FROM (
VALUES
(1, 'Fred'),
(2, 'Wilma'),
(3, 'Barney')
) AS t1
LIMIT 2;Result:
column1 column2 ------- ------- 1 Fred 2 Wilma
This time it returned the limited results as expected.