pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: George Moga <george@dsn.ro>
To: Marc Sherman <msherman@projectile.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Select most recent record?
Date: Wed, 16 May 2001 22:49:18 +0300
Message-ID: <3B02D9BE.32FAA87F@dsn.ro> (raw)
References: <CGEPKMKAIFJINAOACFFEGEJHCGAA.msherman@projectile.ca>

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 ...





view thread (9+ messages)  latest in thread

Message-ID: <3B02D9BE.32FAA87F@dsn.ro>
Permalink:  ../3B02D9BE.32FAA87F@dsn.ro/
Also on:    postgresql.org/message-id/3B02D9BE.32FAA87F@dsn.ro

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: george@dsn.ro, msherman@projectile.ca
  Subject: Re: Select most recent record?
  In-Reply-To: <3B02D9BE.32FAA87F@dsn.ro>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox