I think this was not the case, since I didn't expect the merged result to be ordered.
To be more precise, the query looks like: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit(1) q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit(1) q = q1.union(q2).order_by(Thing.id).all() The q1 returns first filtered element with largest 'a' column, q2 - first with smallest 'a'. So, I guess my question is still valid. On 6 Bir, 17:49, Adrian von Bidder <avbid...@fortytwo.ch> wrote: > On Saturday 06 June 2009 14.18:33 naktinis wrote: > > > I want to use union on two queries, which have different order: > > q1 = Thing.query().order_by(Thing.a) > > q2 = Thing.query().order_by(Thing.b) > > q = q1.union(q2).all() > > SQL doesn't work as you think it does here. > > A UNION does not concatenate the results of the two queries, but is allowed > to return the result in any order. ORDER BY can *then* be applied to the > end result of your union. So even if you use subqueries, the order by in > the subqueries might just be ignored. > > This is to allow the SQL query planner to be clever while building the union > (perhaps a large union over two queries over the same table: if both queries > require a table scan over the large table, the planner might decide to build > the union by scanning the table only once while running both queries in > parallel, so the table is loaded from disk once insead of twice. The UNION > would then contain the resulting rows in more or less random order.) > > But I digress. > > What you want to do is something like: > > SELECT 1 as COL1, ... FROM ... > UNION > SELECT 2 as COL1, ... FROM ... > ORDER BY COL1, ... > > cheers > -- vbi > > > But after this query I get MySQL error message "Incorrect usage of > > UNION and ORDER BY". > > > I guess that this could be because having "SELECT ... UNION SELECT ... > > ORDER BY B", it is not clear whether the second subquery or both > > queries should be ordered using B criteria. I think this can be solved > > by adding brackets to each of the subquery: "(SELECT ...) UNION > > (SELECT ...)". > > > Is there any way to create this query using SQLAlchemy ORM? > > > I am using SQLAlchemy 0.5.4. > > -- > Vertrauen ist gut. Anwalt ist saugeil. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
