From: | Stoffel van Aswegen <Stoffelva(at)gmsi(dot)co(dot)za> |
---|---|
To: | "PostgreSQL Mailing List (E-mail)" <pgsql-novice(at)postgresql(dot)org> |
Subject: | FW: GROUP BY and aggregate functions |
Date: | 2001-07-16 13:35:28 |
Message-ID: | 9D8BB61A24FCD0118E83080036CE9603D2E4E7@ntserver01.gmsi.co.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Oops, I meant:
SELECT *
FROM T1
JOIN (
SELECT foo = MAX(Field1) from T1 GROUP BY ID
) X
ON x.ID = T1.ID AND x.foo = T1.Field1
-----Original Message-----
From: Stoffel van Aswegen
Sent: 16 July 2001 03:31
To: PostgreSQL Mailing List (E-mail)
Subject: RE: [NOVICE] GROUP BY and aggregate functions
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(at)houseag(dot)com]
Sent: 16 July 2001 03:01
To: pgsql-novice(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dorin Grunberg | 2001-07-16 15:09:35 | consistant output for type interval |
Previous Message | Stoffel van Aswegen | 2001-07-16 13:30:37 | RE: GROUP BY and aggregate functions |