Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA29226 for ; Thu, 3 Feb 2000 12:48:00 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id MAA28468; Thu, 3 Feb 2000 12:46:30 -0500 (EST) To: "Vladimir Terziev" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Question about SELECT and ORDER BY In-reply-to: <200002031434.QAA01945@lorna.digsys.bg> References: <200002031434.QAA01945@lorna.digsys.bg> Comments: In-reply-to "Vladimir Terziev" message dated "Thu, 03 Feb 2000 16:34:31 +0200" Date: Thu, 03 Feb 2000 12:46:29 -0500 Message-ID: <28465.949599989@sss.pgh.pa.us> From: Tom Lane "Vladimir Terziev" writes: > I have a query: > SELECT table1.attr1 AS ALIAS, attr2 from table1 UNION ALL > SELECT table2.attr1, attr3 from table2 ORDER BY ALIAS; > I want to have a result from ORDER BY length(ALIAS), but this is imposible. SELECT ... UNION ... ORDER BY is pretty broken in current sources; in particular adding a hidden column to sort on doesn't work when there's a UNION. I've looked a little bit at fixing this, and it looks like it will take the long-threatened querytree restructuring to deal with it in any sensible fashion. Maybe that'll happen for 7.1. In the meantime use Mark Volpe's workaround of only sorting on columns that are in the select list. regards, tom lane