Lists: | pgsql-general |
---|
From: | terry(at)greatgulfhomes(dot)com |
---|---|
To: | "Postgres (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | query problem in 7.2.1: serious planner issue |
Date: | 2002-07-04 17:22:08 |
Message-ID: | 003e01c2237f$53c17000$2766f30a@development.greatgulfhomes.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I cannot sort on a field that I join across tables. Here are the examples:
If I do this:
SELECT offers.lot_id, lots.project_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
AND ...
ORDER BY lot_id
Produces the error:
Error while executing the query; ERROR: ORDER BY 'lot_id' is ambiguous
And if I do this:
If I do this:
SELECT offers.lot_id, lots.project_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
AND ...
ORDER BY lots.lot_id
Produces the error:
Error while executing the query; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT
result must be on one of the result columns
Hmm, it does not like the table name reference either, so what if I rename
the result column...
If I try this:
SELECT offers.lot_id, lots.project_id, offer.lot_id AS offers_lot_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id, offer.lot_id AS offers_lot_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
AND ...
ORDER BY offers_lot_id
Produces the error:
Error while executing the query; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT
result must be on one of the result columns
Just for fun I did ths:
SELECT offers.lot_id, lots.project_id
FROM offers, lots
WHERE offers.lot_id = lots.lot_id
AND ...
UNION
SELECT offers.lot_id, lots_deleted.project_id
FROM offers, lots_deleted
WHERE offers.lot_id = lots_deleted.lot_id
AND ...
ORDER BY offers_lot_id
Which of course does not work but produces the error:
Error while executing the query; ERROR: Attribute 'offer_lot_id' not found
Which distinguishes it from the previous error.
Is there a way to do what I am trying to do???
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com
From: | Herbert Liechti <herbie(at)thinx(dot)ch> |
---|---|
To: | <terry(at)greatgulfhomes(dot)com> |
Cc: | "Postgres (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: query problem in 7.2.1: serious planner issue |
Date: | 2002-07-04 18:05:03 |
Message-ID: | Pine.LNX.4.33.0207042004430.15109-100000@standbyme.thinx.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 4 Jul 2002 terry(at)greatgulfhomes(dot)com wrote:
> I cannot sort on a field that I join across tables. Here are the examples:
>
> If I do this:
> SELECT offers.lot_id, lots.project_id
> FROM offers, lots
> WHERE offers.lot_id = lots.lot_id
> AND ...
> UNION
> SELECT offers.lot_id, lots_deleted.project_id
> FROM offers, lots_deleted
> WHERE offers.lot_id = lots_deleted.lot_id
> AND ...
> ORDER BY lot_id
ORDER BY 1
Regards Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti http://www.thinx.ch
The content management company. Visit http://www.contentx.ch
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | terry(at)greatgulfhomes(dot)com |
Cc: | "Postgres (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: query problem in 7.2.1: serious planner issue |
Date: | 2002-07-04 18:22:43 |
Message-ID: | 4587.1025806963@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
terry(at)greatgulfhomes(dot)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