CODEWITHSUNDEEP
pythonsqlsqlitesqlalchemyunion
Will
Will

Reputation: 75655

Sort by a column in a union query in SqlAlchemy SQLite

As explained in this question, you can use string literals to do order by in unions.

For example, this works with Oracle:

querypart1 = select([t1.c.col1.label("a")]).order_by(t1.c.col1).limit(limit)
querypart2 = select([t2.c.col2.label("a")]).order_by(t2.c.col2).limit(limit)
query = querypart1.union_all(querypart2).order_by("a").limit(limit)

The order-by can take a string literal, which is the name of the column in the union result.

(There are gazillions of rows in partitioned tables and I'm trying to paginate the damn things)

When running against SQLite3, however, this generates an exception:

sqlalchemy.exc.OperationalError: (OperationalError) near "ORDER": syntax error

How can you order by the results of a union?

Upvotes: 2

Views: 2459

Answers (1)

CL.
CL.

Reputation: 180141

The queries that are part of a union query must not be sorted.

To be able to use limits inside a compound query, you must wrap the individual queries inside a separate subquery:

SELECT * FROM (SELECT ... LIMIT ...)
UNION ALL
SELECT * FROM (SELECT ... LIMIT ...)

q1 = select(...).limit(...).subquery()
q2 = select(...).limit(...).subquery()
query = q1.union_all(q2)...

Upvotes: 4

Related Questions