From: | George Moga <george(at)dsn(dot)ro> |
---|---|
To: | Marc Sherman <msherman(at)projectile(dot)ca>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Select most recent record? |
Date: | 2001-05-16 19:49:18 |
Message-ID: | 3B02D9BE.32FAA87F@dsn.ro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Marc Sherman wrote:
> ......
>
> Heh. I obviously simplified my situation too much.
>
> This is closer to what I've really got:
>
> create table user (userid int4 primary key, groupid int4);
> create table log (userid int4, timestamp datetime, value int4);
>
> I need to select sum(value) for each group, where the values chosen
> are the newest log entry for each group member that is before a cutoff
> date (April 1, 2001).
>
> Here's what I'm currently using:
>
> select user.groupid, sum(l1.value)
> from log as l1, user
> where user.userid=log.userid
> and log.timestamp in (
> select max(timestamp) from log
> where log.timestamp<'2001-04-01'
> and log.userid=l1.userid)
> group by user.groupid;
>
> When I first posted, this was _very_ slow. I've since improved
> it by adding an index on log(userid,timestamp) - now it's just
> slow. If anyone knows how to make it faster, I'd appreciate it.
>
> - Marc
Try something like this:
SELECT
user.groupid, sum(l1.value)
FROM
log as l1, user
WHERE
user.userid = l1.userid and
l1.timestamp = (
SELECT
max(timestamp) from log
WHERE
log.timestamp < '2001-04-01' and
log.userid = l1.userid
)
GROUP by user.groupid;
1. you use in the same query both "log" and "l1" for the same table: "log as
l1";
2. you use log.timestamp in () ... but in this case you have ony one value ...
use "=" instead "in".
==================================================
George Moga,
Data Systems Srl
Slobozia, ROMANIA
P.S. Sorry for my english ...
From | Date | Subject | |
---|---|---|---|
Next Message | Ligia Pimentel | 2001-05-16 21:29:57 | Re: fatal ERROR running simple join query... |
Previous Message | G. Anthony Reina | 2001-05-16 19:43:29 | "avg" function for arrays? |