How can I get the last element out of GROUP BY sets?
От | Robert Creager |
---|---|
Тема | How can I get the last element out of GROUP BY sets? |
Дата | |
Msg-id | 20040118212834.7c667b9b.Robert_Creager@LogicalChaos.org обсуждение исходный текст |
Ответы |
Re: How can I get the last element out of GROUP BY sets?
Re: How can I get the last element out of GROUP BY sets? |
Список | pgsql-sql |
I'm trying to produce summary data from a table (using PGSQL 7.4.1): CREATE TABLE readings( "when" timestamp, value integer ); The summary will be based on various time periods. I've been using date_trunc( 'hour', "when" ) and GROUP BY for the min/max/average readings with no problems. But, one piece of data I need is the last value for each GROUP BY period. Alas, I cannot figure out how to do this. If I wanted to loop from a script, I could, for instance, execute the following for each GROUP BY period (filling in ? appropriately): SELECT date_trunc( 'hour', "when" ), value FROM readings WHERE date_trunc( 'hour', "when" )::timestamp = ? ORDER BY "when" DESC LIMIT 1 But, I figure there's probably some what to do this in SQL. Any help? Thanks, Rob -- 21:12:24 up 21 days, 11:00, 4 users, load average: 2.23, 1.69, 1.28
В списке pgsql-sql по дате отправления: