Help to sample a table

From: Vittorio <vdemart1(at)tin(dot)it>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Help to sample a table
Date: 2006-07-06 15:52:23
Message-ID: 10c448a34a3.vdemart1@tin.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear friends,

in postgresql 8.0.7 I have the following table

\d
basedati

Colonna | Tipo |
Modificatori
----------+-----------------------------+--------------

data_ora | timestamp without time zone |
cod_wmo | character
(5) |
t_aria | numeric(5,1) |

ur | integer |

looking like:


data_ora | cod_wmo | t_aria | ur
---------------------+---------
+--------+----
2006-07-05 00:50:00 | 16235 | 22.0 | 74
2006-07-05
02:50:00 | 16235 | 19.0 | 80
2006-07-05 09:50:00 | 16235 |
28.0 | 45
2006-07-05 12:00:00 | 16235 | 31.0 | 41
2006-07-06 00:
00:00 | 16235 | 20.1 | 93
2006-07-06 00:50:00 | 16235 | 20.0 |
93
2006-07-06 06:50:00 | 16235 | 25.0 | 65
2006-07-06 11:50:00 |
16235 | 30.0 | 49
2006-07-06 12:00:00 | 16235 | 29.5 | 51

2006-07-05 03:00:00 | 16242 | 19.9 | 64
2006-07-05 03:15:00 |
16242 | 20.0 | 69
2006-07-05 10:15:00 | 16242 | 28.0 | 39

2006-07-05 12:00:00 | 16242 | 28.6 | 39
2006-07-06 00:00:00 |
16242 | 22.2 | 71
2006-07-06 00:15:00 | 16242 | 22.0 | 74

2006-07-06 00:45:00 | 16242 | 23.0 | 64
2006-07-06 01:15:00 |
16242 | 21.0 | 74
2006-07-06 10:15:00 | 16242 | 27.0 | 56

2006-07-06 12:00:00 | 16242 | 25.6 | 72

What I would like to be
able to sample from it (and I'm unable to!!) is:

for each cod_wmo and
each day (that is to_char(data_ora, 'YYYY-MM-DD')), the complete record
in correspondence of the maximum of t_aria.
e.g.
data_ora
| cod_wmo | t_aria | ur
---------------------+---------+--------+----

2006-07-05 12:00:00 | 16235 | 31.0 | 41
2006-07-06 11:50:00 |
16235 | 30.0 | 49
2006-07-05 12:00:00 | 16242 | 28.6 | 39

2006-07-06 10:15:00 | 16242 | 27.0 | 56

Please help this absolute
beginner

Ciao
Vittorio

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2006-07-06 15:52:30 Re: Modeling Tool
Previous Message Szymic1 2006-07-06 14:37:17 FOR from query - is it a bug ?