Help with select with max and min please
От | Jose Maria Terry |
---|---|
Тема | Help with select with max and min please |
Дата | |
Msg-id | 4C5EFBC4.1080101@tssystems.net обсуждение исходный текст |
Ответы |
Re: Help with select with max and min please
(Edoardo Panfili <edoardo@aspix.it>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: