Skip site navigation (1) Skip section navigation (2)

Re: Need to overcome UNION / ORDER BY restriction

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Timo" <siroco(at)suomi24(dot)fi>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Need to overcome UNION / ORDER BY restriction
Date: 2003-09-29 18:06:39
Message-ID: 26825.1064858799@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"Timo" <siroco(at)suomi24(dot)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

In response to

pgsql-sql by date

Next:From: Franco Bruno BorghesiDate: 2003-09-29 18:08:21
Subject: Re: SQL Syntax problem
Previous:From: Bruce MomjianDate: 2003-09-29 17:54:48
Subject: Re: does postgresql execute unions in parallel?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group