From: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: complex column definition in query
Date: Wed, 03 Jun 2009 07:53:07 -0500
Message-ID: <87prdlo4wc.fsf@patagonia.sebmags.homelinux.org> (raw)
References: <87iqjep9hy.fsf@patagonia.sebmags.homelinux.org>
<20090603050432.GA18756@a-kretschmer.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spluque@gmail.com
Subject: Re: complex column definition in query
In-Reply-To: <87prdlo4wc.fsf@patagonia.sebmags.homelinux.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox