To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: Re: complex column definition in query
Date: Wed, 03 Jun 2009 07:53:07 -0500
Organization: Church of Emacs
Lines: 40
Message-ID: <87prdlo4wc.fsf@patagonia.sebmags.homelinux.org>
References: <87iqjep9hy.fsf@patagonia.sebmags.homelinux.org>
	<20090603050432.GA18756@a-kretschmer.de>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.0.94 (gnu/linux)
Cancel-Lock: sha1:S1ZY2dW1cDPsQPEX1aH0XqBtulA=

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