ORDER BY in UNION in postgres 7.3.9

[Robert Welz]

"SELECT a.*, m.mailbox_name FROM addresses a, mailboxes m,
link_mailbox_addresses lma "
+ "WHERE NOT temp_address AND NOT delete AND a.address_ref > 0 AND
protocol <> ? "
+ "AND a.address_ref = lma.address_ref AND lma.mailbox_ref =
m.mailbox_ref "
+ "UNION SELECT *, '' FROM addresses WHERE address_ref NOT IN "
+ "(SELECT address_ref from link_mailbox_addresses) "
+ "AND NOT temp_address AND NOT delete AND address_ref > 0 AND protocol
<> ? "
+ "ORDER BY site_name ASC, recipient_name ASC");

when last line is replaced with

"ORDER BY UPPER(site_name) ASC, recipient_name ASC"

I get ( postgres 7.3.9) ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT
result must be on one of the result columns
which I don't understand. Can someone explain?

Thank you very much !!

regards,
Robert

[Laurenz Albe]

It is a limitation that is documented in
http://www.postgresql.org/docs/current/static/queries-order.html

I guess that it is too hard to distinguish between ORDER BY clauses
that are expressions on the result rows and other expressions, but
I don't know.

You can easily work around it:

CREATE TABLE a (id integer PRIMARY KEY, val text);
INSERT INTO a VALUES (1, 'one');
INSERT INTO a VALUES (2, 'two');

CREATE TABLE b (id integer PRIMARY KEY, val text);
INSERT INTO b VALUES (1, 'ONE HUNDRED');
INSERT INTO b VALUES (2, 'TWO HUNDRED');

SELECT * FROM a UNION SELECT * FROM b ORDER BY val;

id | val
----+-------------
1 | one
1 | ONE HUNDRED
2 | two
2 | TWO HUNDRED
(4 rows)

Ok, works.

SELECT * FROM a UNION SELECT * FROM b ORDER BY upper(val);

ERROR: invalid UNION/INTERSECT/EXCEPT ORDER BY clause
DETAIL: Only result column names can be used, not expressions or functions.
HINT: Add the expression/function to every SELECT, or move the UNION
into a FROM clause.

Ah, a useful hint!

SELECT * FROM (SELECT * FROM a UNION SELECT * FROM b) dummy
ORDER BY upper(val);

id | val
----+-------------
1 | one
1 | ONE HUNDRED
2 | two
2 | TWO HUNDRED
(4 rows)

There you go!

Yours,
Laurenz Albe

[Robert Welz]

Laurenz Albe <inv...@spam.to.invalid> wrote:

...

>
> SELECT * FROM (SELECT * FROM a UNION SELECT * FROM b) dummy
> ORDER BY upper(val);
>
> id | val
> ----+-------------
> 1 | one
> 1 | ONE HUNDRED
> 2 | two
> 2 | TWO HUNDRED
> (4 rows)
>
> There you go!
>
> Yours,
> Laurenz Albe

Great! Thank you very much!

Robert

[paedru fernando]

...........................................


Great solution!! Thanks - Laurenz Albe . IF you ever be to India (Tamilnadu). your beer's on me.