Help with select with max and min please
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
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.42Where 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
Show quoted text
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.742Can 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
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.42Where 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