If you’re getting an error that reads ‘Parse error: near “ORDER”‘ in SQLite, it could be that you’re trying to use the ORDER BY clause when using the VALUES clause as a stand alone statement.

Although we can certainly use the VALUES clause as a stand alone SQL statement, we can’t apply the ORDER BY clause against it.

However all is not lost. Below is an example of how we can sort the results of the VALUES statement.

Example of Error

Here’s an example of code that produces the error:

VALUES 
  (1, 'Fred'), 
  (2, 'Wilma'), 
  (3, 'Barney')
ORDER BY column2;

Result:

Parse error: near "ORDER": syntax error
     (1, 'Fred'),    (2, 'Wilma'),    (3, 'Barney') ORDER BY column2;
                                      error here ---^

This error tells us that the error occurred at the ORDER BY clause. Given the VALUES statement doesn’t accept an ORDER BY clause, it’s a pretty safe bet that the ORDER BY clause is what caused the error.

Solution 1

Although we can’t place an ORDER BY clause into our VALUES statement, what we can do is place our VALUES statement into a subquery, and then sort the outer query:

SELECT * FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
) AS t1
ORDER BY column2;

Result:

column1  column2
-------  -------
3        Barney 
1        Fred   
2        Wilma  

This time it sorted the results as expected.

Solution 2

Another way of dealing with the issue is to remove the ORDER BY clause altogether. We could adjust our data so that it’s already in the desired order:

VALUES
  (3, 'Barney'),
  (1, 'Fred'), 
  (2, 'Wilma');

Result:

column1  column2
-------  -------
3        Barney 
1        Fred   
2        Wilma  

Having said that, it’s important to remember that whenever there’s no ORDER BY clause, the order in which the rows are returned in SQL queries is undefined. So use this solution cautiously.