Date: Wed, 3 Jun 2009 07:04:32 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: complex column definition in query
Message-ID: <20090603050432.GA18756@a-kretschmer.de>
References: <87iqjep9hy.fsf@patagonia.sebmags.homelinux.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <87iqjep9hy.fsf@patagonia.sebmags.homelinux.org>
User-Agent: Mutt/1.5.9i

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)


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net