pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect most recent record?
9+ messages / 6 participants
[nested] [flat]
* Select most recent record?
@ 2001-05-16 13:28 Marc Sherman <msherman@projectile.ca>
2001-05-16 14:35 ` Re: Select most recent record? Tom Lane <tgl@sss.pgh.pa.us>
2001-06-21 20:50 ` RE: Select most recent record? Mark Hamby <mark@archer.leavenworth.army.mil>
0 siblings, 2 replies; 9+ messages in thread
From: Marc Sherman @ 2001-05-16 13:28 UTC (permalink / raw)
To: pgsql-sql
Hi, I was hoping I could get some help with a select statement.
I have a log table with three columns: id int4, timestamp datetime,
value int4.
For any given ID, there will be a large number of rows, with
different timestamps and values.
I'd like to select the newest (max(timestamp)) row for each id,
before a given cutoff date; is this possible?
The best I've been able to come up with is the rather ugly (and
very slow):
select * from log as l1 where timestamp in
(select max(timestamp) from log where id=l1.id and
timestamp<'2001-01-01' group by id);
There must be a better way to do this; any tips?
Thanks,
- Marc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
@ 2001-05-16 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-16 14:43 ` Re: Select most recent record? Andrew Perrin <andrew_perrin@unc.edu>
2001-05-16 14:52 ` RE: Select most recent record? Marc Sherman <msherman@projectile.ca>
1 sibling, 2 replies; 9+ messages in thread
From: Tom Lane @ 2001-05-16 14:35 UTC (permalink / raw)
To: Marc Sherman <msherman@projectile.ca>; +Cc: pgsql-sql
"Marc Sherman" <msherman@projectile.ca> writes:
> I'd like to select the newest (max(timestamp)) row for each id,
> before a given cutoff date; is this possible?
select * from log order by timestamp desc limit 1;
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
2001-05-16 14:35 ` Re: Select most recent record? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-05-16 14:43 ` Andrew Perrin <andrew_perrin@unc.edu>
2001-05-16 14:50 ` Re: Select most recent record? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Andrew Perrin @ 2001-05-16 14:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marc Sherman <msherman@projectile.ca>; pgsql-sql
Except that he wants max(timestamp) by id; perhaps a GROUP BY would
help, something like (untested):
select max(timestamp) from log group by id;
Tom Lane wrote:
>
> "Marc Sherman" <msherman@projectile.ca> writes:
> > I'd like to select the newest (max(timestamp)) row for each id,
> > before a given cutoff date; is this possible?
>
> select * from log order by timestamp desc limit 1;
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
--------------------------------------------------------------
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * andrew_perrin@unc.edu
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
2001-05-16 14:35 ` Re: Select most recent record? Tom Lane <tgl@sss.pgh.pa.us>
2001-05-16 14:43 ` Re: Select most recent record? Andrew Perrin <andrew_perrin@unc.edu>
@ 2001-05-16 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2001-05-16 14:50 UTC (permalink / raw)
To: Andrew Perrin <andrew_perrin@unc.edu>; +Cc: Marc Sherman <msherman@projectile.ca>; pgsql-sql
Andrew Perrin <andrew_perrin@unc.edu> writes:
> Except that he wants max(timestamp) by id; perhaps a GROUP BY would
> help, something like (untested):
Oh, I'm sorry, I missed the "for each id" bit.
> select max(timestamp) from log group by id;
That gives you the max timestamp for each id, all right, but not the
rest of the row in which the max timestamp occurs. I know of no good
way to get that in standard SQL. If you don't mind being nonstandard,
this is exactly the problem SELECT DISTINCT ON is meant for:
select distinct on (id) * from log order by id, timestamp desc;
See the SELECT reference page for more about this.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
2001-05-16 14:35 ` Re: Select most recent record? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-05-16 14:52 ` Marc Sherman <msherman@projectile.ca>
2001-05-16 19:49 ` Re: Select most recent record? George Moga <george@dsn.ro>
1 sibling, 1 reply; 9+ messages in thread
From: Marc Sherman @ 2001-05-16 14:52 UTC (permalink / raw)
To: pgsql-sql
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Marc Sherman" <msherman@projectile.ca> writes:
> > I'd like to select the newest (max(timestamp)) row for each id,
> > before a given cutoff date; is this possible?
>
> select * from log order by timestamp desc limit 1;
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
2001-05-16 14:35 ` Re: Select most recent record? Tom Lane <tgl@sss.pgh.pa.us>
2001-05-16 14:52 ` RE: Select most recent record? Marc Sherman <msherman@projectile.ca>
@ 2001-05-16 19:49 ` George Moga <george@dsn.ro>
0 siblings, 0 replies; 9+ messages in thread
From: George Moga @ 2001-05-16 19:49 UTC (permalink / raw)
To: Marc Sherman <msherman@projectile.ca>; 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 ...
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
@ 2001-06-21 20:50 ` Mark Hamby <mark@archer.leavenworth.army.mil>
2001-06-21 19:27 ` RE: Select most recent record? Marc Sherman <msherman@projectile.ca>
1 sibling, 1 reply; 9+ messages in thread
From: Mark Hamby @ 2001-06-21 20:50 UTC (permalink / raw)
To: Marc Sherman <msherman@projectile.ca>; pgsql-sql
Marc,
Did you ever get your problem solved to your satisfaction?
We have a very simular problem with a historical database
containing typically 5K id and updates to data every few
seconds. We tried unsuccessfully to optimize queries
such as those already suggested to you. We found the best
means to quickly query the data valid at any given time was
to:
1. Have a TIME and ENDTIME column in your table.
The ENDTIME column held when the data became invalid.
The initial value for the ENDTIME column was 'infinity'.
2. Have an INSERT rule that set the ENDTIME of all previous
records with same ID to TIME. Here is the rule:
CREATE RULE d_people_ON_INSERT AS
ON INSERT
TO d_people
DO UPDATE d_people
SET endtime = new.time
WHERE ( id = new.id )
AND ( endtime = 'infinity' )
;
3. Selects for any given time are then like the ones below
and very fast.
/* For time '2000-11-20 15:56' */
SELECT * FROM d_people
WHERE ( time <= '2000-11-20 15:56' )
AND ( endtime > '2000-11-20 15:56' );
/* For latest time */
SELECT * FROM d_people
WHERE ( time <= now())
AND ( endtime > now());
Granted, INSERTs take a little longer since they trigger an UPDATE.
But optimized indices help greatly with this.
I highly recommend the following book on the problems and
solutions of temporal data in databases written by the man
who is defining the temporal functionalities of SQL3.
Richard Snodgrass, "Developing Time-Oriented Database Applications in SQL"
http://www.amazon.com/exec/obidos/ASIN/1558604367/qid=993149249/sr=1-4/ref=s
c_b_4/103-3746626-6461410
I hope this helps. It may be overkill, depending on the type and
quantity of your data.
Thanks,
Mark Hamby
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Marc Sherman
> Sent: Wednesday, May 16, 2001 6:28 AM
> To: pgsql-sql List
> Subject: [SQL] Select most recent record?
>
>
> Hi, I was hoping I could get some help with a select statement.
>
> I have a log table with three columns: id int4, timestamp datetime,
> value int4.
>
> For any given ID, there will be a large number of rows, with
> different timestamps and values.
>
> I'd like to select the newest (max(timestamp)) row for each id,
> before a given cutoff date; is this possible?
>
> The best I've been able to come up with is the rather ugly (and
> very slow):
>
> select * from log as l1 where timestamp in
> (select max(timestamp) from log where id=l1.id and
> timestamp<'2001-01-01' group by id);
>
> There must be a better way to do this; any tips?
>
> Thanks,
> - Marc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Select most recent record?
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
2001-06-21 20:50 ` RE: Select most recent record? Mark Hamby <mark@archer.leavenworth.army.mil>
@ 2001-06-21 19:27 ` Marc Sherman <msherman@projectile.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Marc Sherman @ 2001-06-21 19:27 UTC (permalink / raw)
To: Mark Hamby <mark@archer.leavenworth.army.mil>; pgsql-sql
From: Mark Hamby [mailto:mark@archer.leavenworth.army.mil]
>
> Marc,
> Did you ever get your problem solved to your satisfaction?
> We have a very simular problem with a historical database
> containing typically 5K id and updates to data every few
> seconds. We tried unsuccessfully to optimize queries
> such as those already suggested to you. We found the best
> means to quickly query the data valid at any given time was
> to:
[snip]
By adding an index, I was able to get the performance of the
query within a reasonable threshold; it's still slow, but
acceptable. I'll bear your suggestion in mind for next time
I run into this kind of problem, though, if my sol'n can't
handle the data. Thanks,
- Marc
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Select most recent record?
@ 2001-05-16 14:50 Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Ansley (UK) @ 2001-05-16 14:50 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Marc Sherman <msherman@projectile.ca>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Um, I think that the intention was to group by id, and have the max
timestamp per id. If that is the case, then the original query is
probably about the best.
Cheers...
MikeA
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sent: 16 May 2001 15:36
>> To: Marc Sherman
>> Cc: pgsql-sql List
>> Subject: Re: [SQL] Select most recent record?
>>
>>
>> "Marc Sherman" <msherman@projectile.ca> writes:
>> > I'd like to select the newest (max(timestamp)) row for each id,
>> > before a given cutoff date; is this possible?
>>
>> select * from log order by timestamp desc limit 1;
>>
>> regards, tom lane
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister
>> command
>> (send "unregister YourEmailAddressHere" to
>> majordomo@postgresql.org)
>>
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com;
iQA/AwUBOwKTsHympNV/C086EQLIAgCgo6Ypci5XK4iDsNSEzxf5o2XCnLcAoLUd
4qdproc00iw/9aAiVdwQBqm7
=cgTO
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-06-21 20:50 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-16 13:28 Select most recent record? Marc Sherman <msherman@projectile.ca>
2001-05-16 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-16 14:43 ` Andrew Perrin <andrew_perrin@unc.edu>
2001-05-16 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-16 14:52 ` Marc Sherman <msherman@projectile.ca>
2001-05-16 19:49 ` George Moga <george@dsn.ro>
2001-06-21 20:50 ` Mark Hamby <mark@archer.leavenworth.army.mil>
2001-06-21 19:27 ` Marc Sherman <msherman@projectile.ca>
2001-05-16 14:50 RE: Select most recent record? Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
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