Re: UNION with ORDER BY -allowed?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of John
> Sidney-Woollett
> Sent: Thursday, December 02, 2004 10:10 AM
> To: chris(dot)green(at)isbd(dot)co(dot)uk
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] UNION with ORDER BY -allowed?
>
>
> wrap the whole statement in another select

Yes you can do that, but you don't need to. An order by clause explicitly applies the sort action
AFTER the UNION action has been performed.

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com
Fax: (416) 441-9085

>
> select col1, col2, col5, col6 from (
> SELECT
> col1, col2, col5, col6
> FROM
> table
> WHERE
> col2 = 'X'
> UNION
> SELECT
> col3, col4, col5, col6
> FROM
> table
> WHERE
> col4 = 'X'
> ) as t
> order by coalesce(col1, col3);
>
> John Sidney-Woollett
>
> Chris Green wrote:
>
> > It's not quite clear (to me at least) whether I can have a UNION and
> > an ORDER BY in a SELECT statement.
> >
> > What I want to do is:-
> >
> > SELECT
> > col1, col2, col5, col6
> > FROM
> > table
> > WHERE
> > col2 = 'X'
> > UNION
> > SELECT
> > col3, col4, col5, col6
> > FROM
> > table
> > WHERE
> > col4 = 'X'
> > ORDER BY
> > coalesce(col1, col3)
> >
> > Is this valid syntax allowed by postgresql? (I'm not at the system
> > where postgresql is installed at the moment so I can't just try it)
> >
> > col1 and col3 are both DATE columns. col2 and col4 are both
> > varchar(1).
> >
> > I want the ORDER BY to order the result of the UNION.
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>