Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E0E469F9ED7 for ; Thu, 16 Nov 2006 02:42:19 -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 06054-06 for ; Thu, 16 Nov 2006 06:42:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mercury.wardbrook.com (host-84-9-98-118.bulldogdsl.com [84.9.98.118]) by postgresql.org (Postfix) with ESMTP id BF3039F9ECD for ; Thu, 16 Nov 2006 02:42:15 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mercury.wardbrook.com (Postfix) with ESMTP id 3D176532F4; Thu, 16 Nov 2006 06:51:04 +0000 (GMT) Received: by mercury.wardbrook.com (Postfix, from userid 505) id 12150532FF; Thu, 16 Nov 2006 06:51:03 +0000 (GMT) Received: from [192.168.0.64] (unknown [192.168.0.1]) by mercury.wardbrook.com (Postfix) with ESMTP id C63AC532F4; Thu, 16 Nov 2006 06:51:01 +0000 (GMT) Message-ID: <455C089C.50202@wardbrook.com> Date: Thu, 16 Nov 2006 06:43:40 +0000 From: John Sidney-Woollett User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) 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; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS 0.3.12 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/964 X-Sequence-Number: 104060 Alternative options for what they're worth - you'd have to explain to see how efficient they are select id, name from ( select lower(name) as sortkey, id, name from table where name != 'Other' union select 'zzzzz' as sortkey, id, name from table where name = 'Other' ) as t order by sortkey select id, name from ( select case when name='Other' then 'zzzzz' else lower(name) end as sortkey, id, name from table ) as t order by sortkey Notice that the sort will be case insensitive in these examples which may be something that you also want. John 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) -- > 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' > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match