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 -~----------~----~----~----~------~----~------~--~---
