pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFastest way to get max tstamp
2+ messages / 2 participants
[nested] [flat]
* Fastest way to get max tstamp
@ 2006-08-28 18:10 Henry Ortega <juandelacruz@gmail.com>
2006-08-28 19:23 ` Re: Fastest way to get max tstamp Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Henry Ortega @ 2006-08-28 18:10 UTC (permalink / raw)
To: pgsql-sql
This is my table:
name | program | effective | tstamp | rate
------+---------+------------+----------------------------+------
jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20
jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20
jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
I want to get:
name | program | effective | tstamp | rate
------+---------+------------+----------------------------+------
jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
Basically, for effective='08-16-2006', it only gets the latest inserted
record (using tstamp) for that effective date, which is 2006-08-25 11:57:
17.394854.
So what is the quickest way to do this?
I can always do:
Select * from Table t where tstamp=(select max(tstamp) from Table t2 where
t2.name=t.name and t2.effective=t.effective)
but it takes so long since this is a huge table.
Any suggestions?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Fastest way to get max tstamp
2006-08-28 18:10 Fastest way to get max tstamp Henry Ortega <juandelacruz@gmail.com>
@ 2006-08-28 19:23 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Broersma Jr @ 2006-08-28 19:23 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; pgsql-sql
> name | program | effective | tstamp | rate
> ------+---------+------------+----------------------------+------
> jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
> jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
> jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20
> jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20
> jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
> jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
> jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
> jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
>
> I want to get:
> name | program | effective | tstamp | rate
> ------+---------+------------+----------------------------+------
> jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
> jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
> jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
> jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
> jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
> jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
>
> Basically, for effective='08-16-2006', it only gets the latest inserted
> record (using tstamp) for that effective date, which is 2006-08-25 11:57:
> 17.394854.
>
> So what is the quickest way to do this?
> I can always do:
> Select * from Table t where tstamp=(select max(tstamp) from Table t2 where
> t2.name=t.name and t2.effective=t.effective)
> but it takes so long since this is a huge table.
>
> Any suggestions?
SELECT name, program, effective, tstamp, rate
FROM TABLE AS T1
JOIN
(
SELECT max(tstamp) as maxtstamp
FROM Table
WHERE tstamp between current_timestamp - interval '7 days' and current_timestamp
GROUP BY name, program, effective
) AS T2
ON (T1.tstamp = T2.maxtstamp)
;
A smaller date range on a large table will really speed up your query also. If you really need to
see the results of the same table over and over again, a materialized view(i.e. push the query
results into a table and then add incremental updates over time) would probably work better for you.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-08-28 19:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-28 18:10 Fastest way to get max tstamp Henry Ortega <juandelacruz@gmail.com>
2006-08-28 19:23 ` Richard Broersma Jr <rabroersma@yahoo.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