"Scott Morrison" <smorrison@navtechinc.com> writes:
> 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.
Try something like
SELECT ... FROM table
WHERE date <= 'target date'
ORDER BY date DESC
LIMIT 1;
Given an index on the date column, this should be very fast.
If you need additional constraints (like the 'id' in your example),
you can try
SELECT ... FROM table
WHERE id = 'target id' AND date <= 'target date'
ORDER BY id DESC, date DESC
LIMIT 1;
where now you need an index declared on (id, date) (in that order).
The basic idea is to make sure that the row you want is the first one
reached when scanning from a given boundary in the index.
regards, tom lane