X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 25BF6D1B4E7 for ; Mon, 29 Sep 2003 18:07:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 43321-01 for ; Mon, 29 Sep 2003 15:06:40 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 29AE3D1B4F1 for ; Mon, 29 Sep 2003 15:06:39 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h8TI6d6c026826; Mon, 29 Sep 2003 14:06:39 -0400 (EDT) To: "Timo" Cc: pgsql-sql@postgresql.org Subject: Re: Need to overcome UNION / ORDER BY restriction In-reply-to: References: Comments: In-reply-to "Timo" message dated "Mon, 29 Sep 2003 17:27:47 +0300" Date: Mon, 29 Sep 2003 14:06:39 -0400 Message-ID: <26825.1064858799@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/406 X-Sequence-Number: 15384 "Timo" writes: > SELECT * from foo where priority = 1 order by seniority > union select * from foo where priority > 1 order by seniority, priority > but this gives parse error because of the restrictions with ORDER BY and > UNION (I suppose..) You'd need to parenthesize: (SELECT * from foo where priority = 1 order by seniority) UNION ALL (select * from foo where priority > 1 order by seniority, priority) Otherwise the ORDER BY is considered to apply to the whole UNION result (it's effectively got lower binding priority than the UNION). Note also that you *must* use UNION ALL, else UNION will attempt to eliminate duplicates, and mess up the sort order while at it. See also Bruno's solution nearby. Not sure which of these approaches would be faster; try both. regards, tom lane