Re: Fastest way to get max tstamp

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