GROUP BY and aggregate functions

From: Henry House <hajhouse(at)houseag(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: GROUP BY and aggregate functions
Date: 2001-07-16 13:01:15
Message-ID: 20010716060114.A2118@houseag.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Stoffel van Aswegen 2001-07-16 13:30:37 RE: GROUP BY and aggregate functions
Previous Message Barry Hoggard 2001-07-15 02:52:24 Returning a value from an update or insert using DBD::Pg