Thread: complex column definition in query
Hi,
Say we have a table:
SELECT * FROM weather; city | temp_lo | temp_hi | prcp
---------------+---------+---------+-------San Francisco | 46 | 50 | 0.25 San Francisco | 43 | 57 |
0 Hayward | 37 | 54 | Hayward | 30 | 58 | Somewhere | 25 | 60
| Somewhere | 28 | 50 |
(6 rows)
I'm struggling to build a query with a column temp, where the first row
is the lowest temp_lo followed by all the temp_hi for each city. So
this would be the output:
city | temp
---------------+------San Francisco | 43San Francisco | 50 San Francisco | 57 Hayward | 30Hayward |
54 Hayward | 58 Somewhere | 25Somewhere | 60 Somewhere | 50
(6 rows)
Any ideas appreciated! Thanks.
Cheers,
--
Seb
In response to Seb : > Hi, > > Say we have a table: > > SELECT * FROM weather; > city | temp_lo | temp_hi | prcp > ---------------+---------+---------+------- > San Francisco | 46 | 50 | 0.25 > San Francisco | 43 | 57 | 0 > Hayward | 37 | 54 | > Hayward | 30 | 58 | > Somewhere | 25 | 60 | > Somewhere | 28 | 50 | > (6 rows) > > I'm struggling to build a query with a column temp, where the first row > is the lowest temp_lo followed by all the temp_hi for each city. So > this would be the output: > > city | temp > ---------------+------ > San Francisco | 43 > San Francisco | 50 > San Francisco | 57 > Hayward | 30 > Hayward | 54 > Hayward | 58 > Somewhere | 25 > Somewhere | 60 > Somewhere | 50 > (6 rows) > > Any ideas appreciated! Thanks. test=# select * from weather ; city | temp_lo | temp_hi ---------------+---------+---------San Francisco | 46 | 50San Francisco | 43 | 57Hayward | 37 | 54Hayward | 30 | 58 (4 rows) test=*# select city, min(temp_lo) as temp from weather group by city union all select city, temp_hi from weather order by1,2; city | temp ---------------+------Hayward | 30Hayward | 54Hayward | 58San Francisco | 43San Francisco | 50SanFrancisco | 57 (6 rows) HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wed, 3 Jun 2009 07:04:32 +0200, "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > In response to Seb : >> Hi, >> Say we have a table: >> SELECT * FROM weather; city | temp_lo | temp_hi | prcp >> ---------------+---------+---------+------- San Francisco | 46 | 50 | >> 0.25 San Francisco | 43 | 57 | 0 Hayward | 37 | 54 | Hayward | 30 | >> 58 | Somewhere | 25 | 60 | Somewhere | 28 | 50 | (6 rows) >> I'm struggling to build a query with a column temp, where the first >> row is the lowest temp_lo followed by all the temp_hi for each city. >> So this would be the output: >> city | temp ---------------+------ San Francisco | 43 San Francisco | >> 50 San Francisco | 57 Hayward | 30 Hayward | 54 Hayward | 58 >> Somewhere | 25 Somewhere | 60 Somewhere | 50 (6 rows) >> Any ideas appreciated! Thanks. > test=# select * from weather ; city | temp_lo | temp_hi > ---------------+---------+--------- San Francisco | 46 | 50 San > Francisco | 43 | 57 Hayward | 37 | 54 Hayward | 30 | 58 (4 rows) > test=*# select city, min(temp_lo) as temp from weather group by city > union all select city, temp_hi from weather order by 1,2; city | temp > ---------------+------ Hayward | 30 Hayward | 54 Hayward | 58 San > Francisco | 43 San Francisco | 50 San Francisco | 57 (6 rows) Thanks to all that responded on and off list. Is it necessary to ensure that the "FROM" part of the two queries are exactly the same (the real case scenario involves 3 tables)? Cheers, -- Seb
In response to Seb : > > Thanks to all that responded on and off list. Is it necessary to ensure > that the "FROM" part of the two queries are exactly the same (the real No, but both queries should return the same columns (count, typ, order). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net