From: Tom Lane <tgl@sss.pgh.pa.us>
To: Timo <siroco@suomi24.fi>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need to overcome UNION / ORDER BY restriction
Date: Mon, 29 Sep 2003 14:06:39 -0400
Message-ID: <26825.1064858799@sss.pgh.pa.us> (raw)
In-Reply-To: <bl9fh3$1krk$1@news.hub.org>
References: <bl9fh3$1krk$1@news.hub.org>
"Timo" <siroco@suomi24.fi> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, siroco@suomi24.fi
Subject: Re: Need to overcome UNION / ORDER BY restriction
In-Reply-To: <26825.1064858799@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox