Hello everybody out there using PostgreSQL,

There is a table within my database which contains the dates with a
temperature of more than 30°C in 2009 for many different towns.
Here's a short excerpt of it:

town | date
-------------------
London | 2009-07-12
London | 2009-08-04
Berlin | 2009-06-30
Berlin | 2009-07-15
Berlin | 2009-07-29
Paris | 2009-06-25
Paris | 2009-07-05
Paris | 2009-07-06
Paris | 2009-08-14

I want to know the first and the last day with a temperature of more
than 30°C in 2009 for each town. What would be the SELECT statement in
SQL for that?

Thanks in advance,
Julia

Julia Jacobson wrote on 18.07.2010 09:37:
SELECT town, min(date), max(date)
FROM the_table_with_no_name
GROUP BY town;

On 2010-07-18, Julia Jacobson <julia.jacobson (AT) arcor (DOT) de> wrote:
If that table does Sydney (or other southern hemisphere locations)
those results may be misleading.


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Il 18/07/2010 9.37, Julia Jacobson ha scritto:
Does your table contains _only_ the records with more than 30° _or_
_also_ the records of temperatures <30?

The general query, supposing that the table has dates in multiple years
and all temperatures:

select min(date) as oldest, max(date) as latest, town from
temperatures_table where temperature>30 and extract('year', date)='2009'
group by town;

Il 18/07/2010 13.08, Anselmo Canfora ha scritto:

select min(date) as oldest, max(date) as latest, town from
temperatures_table where temperature>30 and extract('year', date)=2009
group by town;

Thanks a lot for your answers.
I'm sorry, but I didn't knew anything about the GROUP BY statement
before, since I'm pretty new to databases.
Using the GROUP BY statement in combination with a normal SELECT query
works perfectly.

On Sun, 18 Jul 2010 09:37:33 +0200, Julia Jacobson wrote:

scott=# copy temp from '/tmp/temp.txt' with delimiter as '|';
COPY 9
Time: 10.307 ms
scott=# select * from temp;
town | temp
---------+------------
London | 2009-07-12
London | 2009-08-04
Berlin | 2009-06-30
Berlin | 2009-07-15
Berlin | 2009-07-29
Paris | 2009-06-25
Paris | 2009-07-05
Paris | 2009-07-06
Paris | 2009-08-14
(9 rows)

Time: 0.374 ms
scott=# select town,min(temp),max(temp)
scott-# from temp
scott-# group by town;
town | min | max
---------+------------+------------
Berlin | 2009-06-30 | 2009-07-29
London | 2009-07-12 | 2009-08-04
Paris | 2009-06-25 | 2009-08-14
(3 rows)

Time: 47.435 ms
scott=#


--
http://mgogala.byethost5.com