Skip site navigation (1) Skip section navigation (2)

Selecting Fields in Union in Subquery

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Ioannis AnagnostopoulosDate: 2012-07-15 01:14:45
Subject: Index slow down insertions...
Previous:From: ktm@rice.eduDate: 2012-07-10 17:47:10
Subject: Re: How to apply a regular expression to a select statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group