terry@greatgulfhomes.com writes:
> I cannot sort on a field that I join across tables. Here are the examples:
> If I do this:
> ORDER BY lot_id
> Produces the error:
> Error while executing the query; ERROR: ORDER BY 'lot_id' is ambiguous
It does? I tried to duplicate this:
test72=# create table offers(lot_id int);
CREATE
test72=# create table lots(lot_id int, project_id int);
CREATE
test72=# create table lots_deleted(lot_id int, project_id int);
CREATE
test72=# SELECT offers.lot_id, lots.project_id FROM offers, lots
test72-# WHERE offers.lot_id = lots.lot_id
test72-# UNION
test72-# SELECT offers.lot_id, lots_deleted.project_id FROM offers, lots_deleted
test72-# WHERE offers.lot_id = lots_deleted.lot_id
test72-# ORDER BY lot_id;
lot_id | project_id
--------+------------
(0 rows)
Your third example (with AS) works fine too once I corrected the typos
(offer.lot_id -> offers.lot_id, etc).
In general, you can ORDER BY the column name or column number of
any output column of the UNION construct. If you want to use a
name then you'd better be sure only one output column has that name.
This is per SQL92 spec; we don't offer any extensions to sort on
non-output columns when we're dealing with a UNION.
regards, tom lane