Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 3FDE39F935B for ; Wed, 15 Nov 2006 20:21:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 24207-06 for ; Wed, 15 Nov 2006 20:21:28 -0400 (AST) X-Greylist: delayed 00:28:45.473066 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 3C51C9F9C52 for ; Wed, 15 Nov 2006 20:21:27 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mail63.csoft.net (mail63.csoft.net [205.205.214.4]) by svr4.postgresql.org (Postfix) with ESMTP id 293495AF8DB for ; Wed, 15 Nov 2006 23:52:41 +0000 (GMT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by mail63.csoft.net (Postfix) with ESMTP id B04D5107BF; Wed, 15 Nov 2006 18:52:33 -0500 (EST) Subject: Re: ORDER BY From: Chris Mulcahy To: George Pavlov Cc: Alexander Staubo , MicroUser , pgsql-general@postgresql.org In-Reply-To: <8C5B026B51B6854CBE88121DBF097A865D43BB@ehost010-33.exch010.intermedia.net> References: <8C5B026B51B6854CBE88121DBF097A865D43BB@ehost010-33.exch010.intermedia.net> Content-Type: text/plain Date: Wed, 15 Nov 2006 17:53:02 -0600 Message-Id: <1163634782.29042.2.camel@gentoobox> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/1148 X-Sequence-Number: 104244 On Wed, 2006-11-15 at 14:59 -0800, George Pavlov wrote: Alas, this suggestion is wrong on two counts: (a) UNION expects a single > ORDER BY that applies to the whole recordset and which has to come at > the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes) -- > maybe you are thinking UNION ALL? So, to follow your advice he may want > a query like this, although it seems quite silly and there still isn't > an ironclad guarantee re. the final result sorting: > > select * from > (select * from foo where name != 'Other' order by name) x > union all > select * from foo where name = 'Other' > > Here ya go. select 1 SortCol, * from foo where name != 'Other' UNION ALL select 2 SortCol, * from foo where name = 'Other' order by SortCol;