Обсуждение: Selecting Fields in Union in Subquery
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;
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
2012/7/13 Tom Burns <tramseyburns@yahoo.com>
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)
This should work ("StateProvence" not gTo.geo_name), but You should remember to use " (quotation mark) when You use camel cased names. If not PG will treat fields names like written in small letters.
You should do something like this:
order by "StateProvence"
not
order by StateProvence
I would like to be able to filter and sort the sub-query in the parent query.
I don't get it, I assume You would like to sort/filter sub-query result in parent query. Then You should remember to use names of sub-query result columns i.e
gTo.geo_name is not visilbe, but geolist."StateProvence" is visible outside sub query
Regards,
Bartek
Bartosz,
or alternate without camel case
select id,country,state
from
(select g.geo_id as "id", g.geo_name as "country", gTo.geo_name as "state"
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')
union
select g.geo_id as "id", g.geo_name as "country", '' as "state"
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) as GeoList
where "id" ='USA'
order by state;
both work
From: Bartosz Dmytrak <bdmytrak@gmail.com>
To: Tom Burns <tramseyburns@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Sunday, July 15, 2012 2:41 PM
Subject: Re: [NOVICE] Selecting Fields in Union in Subquery
2012/7/13 Tom Burns <tramseyburns@yahoo.com>
Thank you so much!
That is the information I needed.
select "Id","Country","State/Provence"
from
(select g.geo_id as "Id", g.geo_name as "Country", gTo.geo_name as "State/Provence"
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) as GeoList
where "Id" ='USA'
order by "State/Provence";
from
(select g.geo_id as "Id", g.geo_name as "Country", gTo.geo_name as "State/Provence"
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) as GeoList
where "Id" ='USA'
order by "State/Provence";
or alternate without camel case
select id,country,state
from
(select g.geo_id as "id", g.geo_name as "country", gTo.geo_name as "state"
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')
union
select g.geo_id as "id", g.geo_name as "country", '' as "state"
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) as GeoList
where "id" ='USA'
order by state;
both work
Again my thanks,
Tom
From: Bartosz Dmytrak <bdmytrak@gmail.com>
To: Tom Burns <tramseyburns@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Sunday, July 15, 2012 2:41 PM
Subject: Re: [NOVICE] Selecting Fields in Union in Subquery
2012/7/13 Tom Burns <tramseyburns@yahoo.com>
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)
This should work ("StateProvence" not gTo.geo_name), but You should remember to use " (quotation mark) when You use camel cased names. If not PG will treat fields names like written in small letters.
You should do something like this:
order by "StateProvence"
not
order by StateProvence
I would like to be able to filter and sort the sub-query in the parent query.
I don't get it, I assume You would like to sort/filter sub-query result in parent query. Then You should remember to use names of sub-query result columns i.e
gTo.geo_name is not visilbe, but geolist."StateProvence" is visible outside sub query
Regards,
Bartek