Обсуждение: GROUP BY and aggregate functions
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
Вложения
You have to join a sub-select (or temp table) which has the Max() criteria. eg: T1 ---- ID Field1 Field2 ... SELECT * FROM T1 JOIN ( SELECT MAX(Field1) from T1 GROUP BY ID ) X ON x.ID = T1.ID -----Original Message----- From: Henry House [mailto:hajhouse@houseag.com] Sent: 16 July 2001 03:01 To: pgsql-novice@postgresql.org Subject: [NOVICE] GROUP BY and aggregate functions 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
On Mon, Jul 16, 2001 at 03:30:37PM +0200, Stoffel van Aswegen wrote: > You have to join a sub-select (or temp table) which has the Max() criteria. > eg: > > T1 > ---- > ID > Field1 > Field2 > ... > > > SELECT * > FROM T1 > JOIN ( > SELECT MAX(Field1) from T1 GROUP BY ID > ) X > ON x.ID = T1.ID Ah, with a subselect it works much better :-). Thanks. -- Henry House OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc
Вложения
Henry House <hajhouse@houseag.com> writes: > I only want the value of id for the row that matches max(postdate). The standard-conforming way involves using a subselect, as someone else already illustrated. A less standard but considerably faster method is to use SELECT DISTINCT ON; there's an example in the SELECT reference page. regards, tom lane