Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 7565C9F9FE2 for ; Thu, 16 Nov 2006 04:59:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 27388-09 for ; Thu, 16 Nov 2006 08:59:01 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx1.magproductions.nl (magfour.magproductions.nl [87.249.97.162]) by postgresql.org (Postfix) with ESMTP id 390109F9FE1 for ; Thu, 16 Nov 2006 04:58:55 -0400 (AST) Received: from cc756500-a.ensch1.ov.home.nl ([82.75.192.119] helo=[172.27.1.6]) by mx1.magproductions.nl with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA:32) (Exim 4.50) id 1Gkd58-0002LM-Vn; Thu, 16 Nov 2006 09:58:51 +0100 Message-ID: <455C2847.5080709@magproductions.nl> Date: Thu, 16 Nov 2006 09:58:47 +0100 From: Alban Hertroys User-Agent: Thunderbird 1.5.0.7 (X11/20060918) MIME-Version: 1.0 To: George Pavlov CC: Alexander Staubo , MicroUser , pgsql-general@postgresql.org Subject: Re: ORDER BY References: <8C5B026B51B6854CBE88121DBF097A865D43BB@ehost010-33.exch010.intermedia.net> In-Reply-To: <8C5B026B51B6854CBE88121DBF097A865D43BB@ehost010-33.exch010.intermedia.net> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/967 X-Sequence-Number: 104063 George Pavlov wrote: >> For larger tables, you may have to resort to a >> union: >> >> select * from foo where name != 'Other' order by name >> union >> select * from foo where name = 'Other' > > 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) -- You can also add an imaginary key to sort on, like select 1, * from foo where name != 'Other' union all select 9, * from foo where name = 'Other' order by 1; Or independent of column order: select 1 AS sort_key, * from foo where name != 'Other' union all select 9 AS sort_key, * from foo where name = 'Other' order by sort_key; Regards, -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //