Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help with select with max and min please

Help with select with max and min please

From
Jose Maria Terry
Date:
  Hello all,

I need to run a query on a table that holds logged data from several
water flow meters.

I need the first and last values (with their associated time) for every
logger in a time range.

I've tried this that returns the min and max time in the desired range
for every logger, but i don't know how to get the associated data (the
row called caudal) for min and max .

select remota_id,min(hora),max(hora) from historicos where hora >
'2010-08-07' and hora <'2010-08-08 00:03' group by remota_id order by
remota_id;

  remota_id |         min         |         max
-----------+---------------------+---------------------
  01        | 2010-08-07 00:00:30 | 2010-08-08 00:00:30
  02        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
  03        | 2010-08-07 00:00:03 | 2010-08-08 00:00:02
  04        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
  05        | 2010-08-07 00:00:01 | 2010-08-08 00:00:02
  06        | 2010-08-07 00:00:02 | 2010-08-08 00:00:02
  07        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
  09        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
(8 filas)

I need some like this:

  remota_id |         min         |         max | min_caudal | max_caudal
-----------+---------------------+---------------------+------------+------------
  01        | 2010-08-07 00:00:30 | 2010-08-08 00:00:30 |     976.04
|     904.21
  02        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 |     829.71
|     764.42

Where min_caudal is the value of caudal in hora = min() and max_caudal
is the same for hora=max()

The data in the table is like this:

select hora,remota_id,caudal from historicos;

         hora         | remota_id |  caudal
---------------------+-----------+----------
  2010-05-21 20:00:06 | 04        | 1201.309
  2010-05-21 20:15:08 | 04        | 1201.309
  2010-05-21 20:30:06 | 04        | 1219.803
  2010-05-21 20:45:06 | 04        | 1225.098
  2010-05-21 21:00:06 | 04        | 1238.359
  2010-05-21 21:15:06 | 04        | 1241.015
  2010-05-21 21:30:06 | 04        | 1241.015
  2010-05-21 21:45:06 | 04        |  1246.33
  2010-05-21 22:00:06 | 04        | 1248.989
  2010-05-21 22:15:06 | 04        | 1235.704
  2010-05-21 22:30:06 | 04        |  1222.45
  2010-05-21 22:45:06 | 04        | 1201.309
  2010-05-21 23:00:06 | 04        | 1203.947
  2010-05-21 23:15:06 | 04        | 1219.803
  2010-05-21 23:30:06 | 04        | 1275.649
  2010-05-21 23:45:06 | 04        | 1280.995
  2010-05-22 00:00:06 | 04        |  1294.38
  2010-05-22 00:15:06 | 04        | 1299.742
  2010-05-22 00:30:06 | 04        |  1294.38
  2010-05-22 00:45:06 | 04        |  1294.38
  2010-05-22 01:00:06 | 04        | 1299.742

Can anyone help me?

Best,


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/11515 - Sun Aug  8 18:16:38 2010
by Markus Madlener @ http://www.copfilter.org

Re: Help with select with max and min please

From
Edoardo Panfili
Date:
On 08/08/10 20.47, Jose Maria Terry wrote:
> Hello all,
>
> I need to run a query on a table that holds logged data from several
> water flow meters.
>
> I need the first and last values (with their associated time) for every
> logger in a time range.
>
> I've tried this that returns the min and max time in the desired range
> for every logger, but i don't know how to get the associated data (the
> row called caudal) for min and max .
>
> select remota_id,min(hora),max(hora) from historicos where hora >
> '2010-08-07' and hora <'2010-08-08 00:03' group by remota_id order by
> remota_id;
>
> remota_id | min | max
> -----------+---------------------+---------------------
> 01 | 2010-08-07 00:00:30 | 2010-08-08 00:00:30
> 02 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
> 03 | 2010-08-07 00:00:03 | 2010-08-08 00:00:02
> 04 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
> 05 | 2010-08-07 00:00:01 | 2010-08-08 00:00:02
> 06 | 2010-08-07 00:00:02 | 2010-08-08 00:00:02
> 07 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
> 09 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
> (8 filas)
>
> I need some like this:
>
> remota_id | min | max | min_caudal | max_caudal
> -----------+---------------------+---------------------+------------+------------
>
> 01 | 2010-08-07 00:00:30 | 2010-08-08 00:00:30 | 976.04 | 904.21
> 02 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 | 829.71 | 764.42
>
> Where min_caudal is the value of caudal in hora = min() and max_caudal
> is the same for hora=max()

this can help?
select remota_id,min(hora),max(hora), (SELECT caudal FROM historicos
WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal,
(SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND
hora=max(ooo.hora)) as max_caudal from historicos AS ooo group by
remota_id order by remota_id;

Edoardo

>
> The data in the table is like this:
>
> select hora,remota_id,caudal from historicos;
>
> hora | remota_id | caudal
> ---------------------+-----------+----------
> 2010-05-21 20:00:06 | 04 | 1201.309
> 2010-05-21 20:15:08 | 04 | 1201.309
> 2010-05-21 20:30:06 | 04 | 1219.803
> 2010-05-21 20:45:06 | 04 | 1225.098
> 2010-05-21 21:00:06 | 04 | 1238.359
> 2010-05-21 21:15:06 | 04 | 1241.015
> 2010-05-21 21:30:06 | 04 | 1241.015
> 2010-05-21 21:45:06 | 04 | 1246.33
> 2010-05-21 22:00:06 | 04 | 1248.989
> 2010-05-21 22:15:06 | 04 | 1235.704
> 2010-05-21 22:30:06 | 04 | 1222.45
> 2010-05-21 22:45:06 | 04 | 1201.309
> 2010-05-21 23:00:06 | 04 | 1203.947
> 2010-05-21 23:15:06 | 04 | 1219.803
> 2010-05-21 23:30:06 | 04 | 1275.649
> 2010-05-21 23:45:06 | 04 | 1280.995
> 2010-05-22 00:00:06 | 04 | 1294.38
> 2010-05-22 00:15:06 | 04 | 1299.742
> 2010-05-22 00:30:06 | 04 | 1294.38
> 2010-05-22 00:45:06 | 04 | 1294.38
> 2010-05-22 01:00:06 | 04 | 1299.742
>
> Can anyone help me?
>
> Best,
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
> AntiVirus: ClamAV 0.95.2/11515 - Sun Aug 8 18:16:38 2010
> by Markus Madlener @ http://www.copfilter.org
>

Re: Help with select with max and min please

From
Jose Maria Terry
Date:
  El 08/08/10 21:49, Edoardo Panfili escribió:
> On 08/08/10 20.47, Jose Maria Terry wrote:
>> Hello all,
>>
>> I need to run a query on a table that holds logged data from several
>> water flow meters.
>>
>> I need the first and last values (with their associated time) for every
>> logger in a time range.
>>
>> I've tried this that returns the min and max time in the desired range
>> for every logger, but i don't know how to get the associated data (the
>> row called caudal) for min and max .
>>
>> select remota_id,min(hora),max(hora) from historicos where hora >
>> '2010-08-07' and hora <'2010-08-08 00:03' group by remota_id order by
>> remota_id;
>>
>> remota_id | min | max
>> -----------+---------------------+---------------------
>> 01 | 2010-08-07 00:00:30 | 2010-08-08 00:00:30
>> 02 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
>> 03 | 2010-08-07 00:00:03 | 2010-08-08 00:00:02
>> 04 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
>> 05 | 2010-08-07 00:00:01 | 2010-08-08 00:00:02
>> 06 | 2010-08-07 00:00:02 | 2010-08-08 00:00:02
>> 07 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
>> 09 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06
>> (8 filas)
>>
>> I need some like this:
>>
>> remota_id | min | max | min_caudal | max_caudal
>> -----------+---------------------+---------------------+------------+------------
>>
>>
>> 01 | 2010-08-07 00:00:30 | 2010-08-08 00:00:30 | 976.04 | 904.21
>> 02 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 | 829.71 | 764.42
>>
>> Where min_caudal is the value of caudal in hora = min() and max_caudal
>> is the same for hora=max()
>
> this can help?
> select remota_id,min(hora),max(hora), (SELECT caudal FROM historicos
> WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal,
> (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND
> hora=max(ooo.hora)) as max_caudal from historicos AS ooo group by
> remota_id order by remota_id;
>
> Edoardo

Thanks, Edoardo!

Works perfect, i've added the date (hora) select and the result is just
what expected:

select remota_id,min(hora),max(hora), (SELECT caudal FROM historicos
WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal,
(SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND
hora=max(ooo.hora)) as max_caudal from historicos AS ooo where hora >
'2010-08-07' and hora <'2010-08-08 00:03' group by remota_id order by
remota_id;

  remota_id |         min         |         max         | min_caudal |
max_caudal
-----------+---------------------+---------------------+------------+------------
  01        | 2010-08-07 00:00:30 | 2010-08-08 00:00:30 |   2785.727 |
2766.883
  02        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 |   1820.309 |
1860.785
  03        | 2010-08-07 00:00:03 | 2010-08-08 00:00:02 |   2296.633 |
2280.154
  04        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 |   1946.548 |
1898.955
  05        | 2010-08-07 00:00:01 | 2010-08-08 00:00:02 |   664.5776 |
984.9826
  06        | 2010-08-07 00:00:02 | 2010-08-08 00:00:02 |    1103.71
|    1185.17
  07        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 |   452.0654 |
410.4259
  09        | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 |   766.8262 |
774.8085
(8 filas)


Best


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/11515 - Sun Aug  8 18:16:38 2010
by Markus Madlener @ http://www.copfilter.org