Re: How can I speed up with query?

How bout

select a.*
from sample a
where exists
(select id
from sample
where date <= '<<thedate>>'
and id = a.id
order by date desc
limit 1)
order by id

Is that what your after? Seems like you could do this w/o the subquery.. but
Im not sure exactly what you want.

Thanks
Chad
----- Original Message -----
From: "Scott Morrison" <smorrison(at)navtechinc(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, February 05, 2003 11:59 AM
Subject: [NOVICE] How can I speed up with query?

> 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 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>