Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNION with ORDER BY -allowed?

UNION with ORDER BY -allowed?

From
Chris Green
Date:
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."

Re: UNION with ORDER BY -allowed?

From
John Sidney-Woollett
Date:
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.
>

Re: UNION with ORDER BY -allowed?

From
Date:
> -----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
>

Re: UNION with ORDER BY -allowed?

From
Date:
> -----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
>

Re: UNION with ORDER BY -allowed?

From
Chris Green
Date:
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."