using aggregate function with more than two columns

Question

I have two tables, stats and stat_log. The stat log is like this:

user_id,stat_id,value,registered

and It logs the value of the stats for the given times. I need every last value from every stat for a given user.

So i want something like this, but with values: select stat,max(registered) from stat_log where uid = 1 group by stat;

stat | max
------+------------
6 | 2009-10-08
1 | 2009-10-08
3 | 2009-10-08
5 | 2009-10-08
7 | 2009-10-08
4 | 2009-10-08

Instead I've got this:

select stat,max(registered),value from stat_log where uid = 1 group by stat,value;

stat | max | value
------+------------+-------
4 | 2009-10-08 | 38
5 | 2009-10-08 | 118
1 | 2009-10-08 | 100
1 | 2009-10-07 | 101
6 | 2009-10-08 | 68
3 | 2009-10-08 | 110
7 | 2009-10-08 | 53

What's the correct query? This is PostgreSQL 8.3.

Answer 1

Not sure whether this is valid SQL for PostgreSQL, but I think you should be able to use something like this:

select * from stat_log
inner join (
    select uid,stat,max(registered) as registered from stat_log group by uid,stat
) as maxrecords on stat_log.stat = maxrecords.stat and
                   stat_log.uid = maxrecords.uid and
                   stat_log.registered = maxrecords.registered
where stat_log.uid = 1

The 'inner join' limits the results to the rows that you're interested in that are returned by the inner subquery.

Answer 2

select
    distinct on (stat_id)
    *
FROM
    stat_log
WHERE
    user_id = 1
order by stat_id desc, registered_desc;

Answer 3

I've done this with some non-standard features in Oracle, but otherwise a subquery or a join is required. I like this form because it's logical to me, but ideally the database would execute it the same way as the joined version.

select stat, registered, value
from stat_log a
where
    id = 1
    and registered = (
        select max(registered)
        from stat_log
        where
            b.id = a.id
            and b.stat = a.stat
        )