From: | Tom Burns <tramseyburns(at)yahoo(dot)com> |
---|---|
To: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Selecting Fields in Union in Subquery |
Date: | 2012-07-13 21:49:34 |
Message-ID: | 1342216174.12212.YahooMailNeo@web122505.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi All Hope You Can Help,
I have a query that selects from a sub-query:
select *
from
(select g.geo_id as "Id", g.geo_name as "Country", gTo.geo_name as "StateProvence"
from geo g,geo gTo, geo_assoc ga
where ga.geo_id = g.geo_id
and ga.geo_id_to = gTo.geo_id
and g.geo_id IN (select geo_id from geo where geo_type_id='COUNTRY')
and g.geo_id = 'USA'
union
select g.geo_id as "Id", g.geo_name as "Country", '' as "State/Provence"
from geo g LEFT OUTER JOIN geo_assoc ga on g.geo_id = ga.geo_id
where g.geo_type_id='COUNTRY' and ga.geo_id is null
and g.geo_id = 'USA') as GeoList
order by 3;
I would like to filter and order by fields returned by the sub-query . For example;
from(select... clause.
The order "order by 3;"works
I can no figure out the syntax for directly addressing fields (for example 'order by StateProvence" or "order by gTo.geo_name" instead of order by 3)
I would like to be able to filter and sort the sub-query in the parent query.
Thanks for any help,
Tom
From | Date | Subject | |
---|---|---|---|
Next Message | Ioannis Anagnostopoulos | 2012-07-15 01:14:45 | Index slow down insertions... |
Previous Message | ktm@rice.edu | 2012-07-10 17:47:10 | Re: How to apply a regular expression to a select statement |