On Jun 6, 2009, at 11:39 AM, naktinis wrote:
> > 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. if youre using limit with order by, you would have to wrap those queries within subqueries in order for UNION to accept them as encapsulated relations. > > > 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 -~----------~----~----~----~------~----~------~--~---
