Обсуждение: UNION with ORDER BY -allowed?
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.
--
Chris Green (chris@areti.co.uk)
"Never ascribe to malice, that which can be explained by incompetence."
wrap the whole statement in another select
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.
>
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Chris Green > Sent: Thursday, December 02, 2004 9:56 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] UNION with ORDER BY -allowed? > > > 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) Yes, provided the columns are the same data types (or you can cast them to make them the same) > > 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. It does, per SQL spec. Nothing less would make sense if you ask me. :) Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085 > > -- > Chris Green (chris@areti.co.uk) > > "Never ascribe to malice, that which can be explained by > incompetence." > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of John > Sidney-Woollett > Sent: Thursday, December 02, 2004 10:10 AM > To: chris.green@isbd.co.uk > Cc: pgsql-general@postgresql.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@greatgulfhomes.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 >
On Thu, Dec 02, 2004 at 10:13:26AM -0500, terry@ashtonwoodshomes.com wrote:
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Chris Green
> > Sent: Thursday, December 02, 2004 9:56 AM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] UNION with ORDER BY -allowed?
> >
> >
> > 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)
> Yes, provided the columns are the same data types (or you can cast them to make them the same)
>
Yes, they are all matching types, what I said just below indicates
this (note that the other columns are the *same* columns).
> >
> > 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.
>
> It does, per SQL spec. Nothing less would make sense if you ask me. :)
>
That's what I thought it was saying, thanks for confirming it.
--
Chris Green (chris@areti.co.uk)
"Never ascribe to malice, that which can be explained by incompetence."