I would like to know if there is a more efficient way to perform a query.
I have a table (see below) with an ID, a date, and a value. The date
specifies when the entry was added. I want to query the table to determine
what the value was on a specific day.
I have the following query, which works quite well but it is very slow (the
real table I'm using has 200000 records)... I'm sure the slowness is due to
the subselect... does anybody know any way to make this query faster?
Thanks in advance,
Scott
My current query:
select a.* from sample a where (id,date) in (select a.id,max(date) from
sample where date<='<<the date>>' and id=a.id) order by id;
So with the data below, filling in '2003-02-01' for <<the date>> gives:
id | date | value
----+------------+-------
1 | 2003-02-01 | 12
2 | 2003-02-01 | 9
3 | 2003-02-01 | 2
4 | 2003-02-01 | 11
And filling in '2003-02-04' for <<the date>> gives:
id | date | value
----+------------+-------
1 | 2003-02-04 | 21
2 | 2003-02-01 | 9
3 | 2003-02-01 | 2
4 | 2003-02-03 | 12
Here is the table layout and the sample data I'm using:
Table "sample"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
date | date | not null
value | integer | not null
Primary key: sample_pkey
id | date | value
----+------------+-------
1 | 2003-02-01 | 12
1 | 2003-02-02 | 16
1 | 2003-02-04 | 21
2 | 2003-02-01 | 9
3 | 2003-02-01 | 2
4 | 2003-02-01 | 11
4 | 2003-02-03 | 12
(7 rows)