Обсуждение: 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