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