CODEWITHSUNDEEP
sqlite
Tominator
Tominator

Reputation: 1224

Sqlite3 order by not working for union

I have this simplified example:

CREATE TABLE test1 ( 
    id                     INTEGER        NOT NULL
                                          PRIMARY KEY AUTOINCREMENT,
    status                 TEXT           NOT NULL
                                          DEFAULT 'waiting');
CREATE TABLE test2 ( 
    id                     INTEGER        NOT NULL
                                          PRIMARY KEY AUTOINCREMENT,
    status                 TEXT           NOT NULL
                                          DEFAULT 'waiting');

And I run this query:

SELECT status
FROM test1

UNION

SELECT status
FROM test2

ORDER BY CASE status
WHEN 'accepted' THEN 1
WHEN 'invited' THEN 2
WHEN 'waiting' THEN 3
WHEN 'cancelled' THEN 4
ELSE 5
END

With hopes of getting combined ordering of 1-5 based on textual content of the field.

However, I get this error:

Error while executing query: 1st ORDER BY term does not match any column in the result set

When I remove either part of the UNION, the query works fine, so it's not related to the "case when" construct.

As far as I can see, both union parts have the same column named 'status', so I don't understand why I can't order the union.

It works in mysql, but I want to get it working in sqlite as well, if possible...

Upvotes: 2

Views: 1119

Answers (1)

CL.
CL.

Reputation: 180210

The documentation says:

If the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column.

So you have to make the ordering expression an output column:

SELECT status,
       CASE ... END AS order_me
FROM test1
UNION ALL
SELECT status,
       CASE ... END
FROM test2
ORDER BY order_me

To avoid the duplication, you could use a subquery:

SELECT status
FROM (SELECT status
      FROM test1
      UNION ALL
      SELECT status
      FROM test2)
ORDER BY CASE status ...
         END

Upvotes: 4

Related Questions