pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcomplex column definition in query
4+ messages / 2 participants
[nested] [flat]
* complex column definition in query
@ 2009-06-02 22:16 Seb <spluque@gmail.com>
2009-06-03 05:04 ` Re: complex column definition in query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Seb @ 2009-06-02 22:16 UTC (permalink / raw)
To: pgsql-sql
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.
Cheers,
--
Seb
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: complex column definition in query
2009-06-02 22:16 complex column definition in query Seb <spluque@gmail.com>
@ 2009-06-03 05:04 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-06-03 12:53 ` Re: complex column definition in query Seb <spluque@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2009-06-03 05:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: complex column definition in query
2009-06-02 22:16 complex column definition in query Seb <spluque@gmail.com>
2009-06-03 05:04 ` Re: complex column definition in query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2009-06-03 12:53 ` Seb <spluque@gmail.com>
2009-06-03 13:23 ` Re: complex column definition in query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Seb @ 2009-06-03 12:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: complex column definition in query
2009-06-02 22:16 complex column definition in query Seb <spluque@gmail.com>
2009-06-03 05:04 ` Re: complex column definition in query A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-06-03 12:53 ` Re: complex column definition in query Seb <spluque@gmail.com>
@ 2009-06-03 13:23 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2009-06-03 13:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-06-03 13:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-02 22:16 complex column definition in query Seb <spluque@gmail.com>
2009-06-03 05:04 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-06-03 12:53 ` Seb <spluque@gmail.com>
2009-06-03 13:23 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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