GROUP BY and aggregate functions
От | Henry House |
---|---|
Тема | GROUP BY and aggregate functions |
Дата | |
Msg-id | 20010716060114.A2118@houseag.com обсуждение исходный текст |
Ответы |
Re: GROUP BY and aggregate functions
|
Список | pgsql-novice |
It appears that ther is no way to use an aggregate function ( max() ) without also using GROUP BY. I'm trying to do something different, though. I have a table of values and dates. I would like to return only the most recent row for each value (which could be a username, to keep track of the user's current widget count while preserving old values for recordkeeping). Like this: testdb=> select val, max(postdate) from status group by val; val| max ----+------------------------ 0 | 2001-07-16 05:31:01-07 1 | 2001-07-16 05:31:12-07 (2 rows) except that I also need the (user's, whatever's) unique id to do any joins. This is as close as I can get: there is now one row for each value of id. I only want the value of id for the row that matches max(postdate). testdb=> select val, max(postdate), id from status group by val, id; val| max |id ----+------------------------+--- 0 | 2001-07-16 04:43:02-07 | 0 0 | 2001-07-16 05:31:01-07 | 3 1 | 2001-07-16 04:43:02-07 | 2 1 | 2001-07-16 05:31:12-07 | 3 (4 rows) Any advice would be appreciated! -- Henry House OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc
Вложения
В списке pgsql-novice по дате отправления: