Re:

Thanks to everybody for your replies.  For some reason that has went through
as two different threads.

Anyways, I do have indices on both the date and id fields.

The queries suggested by Tom Lane and Doug Silver do not do exactly what I
want to do... but I believe that's because I did not form what it was
correctly.

I want a query which lists all of the ids from the table with the date which
is closest to (but not past) a given date.

For example, the statement
select * from sample where date<='2003-02-04' order by id,date;
will return:
 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 **

whereas I only want the fields marked with ** (the most recent date on or
before the one specified in the query)

Greg Sabino Mullane (in the other thread) suggested the following alternate,
but it ran slightly slower on my machine than the original.

SELECT id, date, value FROM sample a WHERE date <='2003-01-01' AND oid =
(SELECT oid FROM sample WHERE id = a.id AND DATE <='2003-01-01' ORDER BY
date DESC LIMIT 1)
ORDER BY id;

Thanks,
Scott




-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Doug Silver
Sent: Thursday, February 06, 2003 3:03 PM
To: Scott Morrison; pgsql-novice@postgresql.org
Subject: Re: [NOVICE]


On Tuesday 04 February 2003 03:35 pm, Scott Morrison wrote:
> 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)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

First, you didn't say if you've indexed the date field, which you should do
if
you're searching on that field more than once.  That will certainly help out
with the speed.  Also, that query seems a bit overdone, how about this:

select * from sample where date<='<<the date>>' order by id,date;

You're only getting information from a single table, so I don't think the
subselect was necessary, though I might be missing something since the
coffee
has worn off ;)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Urchin Software Corp.    http://www.urchin.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html