[SOLVED] SQLite: ...EXCEPT ... ORDER BY RANDOM()

SQLite: ...EXCEPT ... ORDER BY RANDOM()

This query:

SELECT colour FROM ColourView
EXCEPT SELECT colour FROM SkinColourExceptionsView
WHERE race = 'Human' ORDER BY RANDOM() LIMIT 1

Produces the following exception:

1st ORDER BY term does not match any column in the result set

However, changing ORDER BY RANDOM() to ORDER BY colour is able to produce results (sorted alphabetically by colour rather than randomly).

The following suggests that ORDER BY RANDOM() LIMIT 1 should work: Select random row from a sqlite table.

Can someone explain why my query doesn't work?

Solution

In a compound query, the ORDER BY term(s) must match some column in the result set.

You can work around this restriction by moving the compound query into a subquery:

SELECT colour
FROM (SELECT colour FROM ColourView
      EXCEPT
      SELECT colour FROM SkinColourExceptionsView WHERE race = 'Human')
ORDER BY random()
LIMIT 1;

Alternatively, rewrite the query to use a different filter mechanism:

SELECT colour
FROM ColourView
WHERE colour NOT IN (SELECT colour
                     FROM SkinColourExceptionsView
                     WHERE race = 'Human')
ORDER BY random()
LIMIT 1;