> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Joel Burton
> Sent: Thursday, May 16, 2002 4:14 PM
> To: Gregory Brauer; pgsql-sql@postgresql.org
> Subject: Re: [SQL] SQL over my head...
>
>
> I think that
>
> SELECT F0.id
> FROM Foo AS F0
> JOIN Bar AS B0 ON (F0.id=B0.id)
> WHERE ts =
> (SELECT MAX(ts)
> FROM Foo AS F1,
> Bar as B1
> WHERE ts < CURRENT_TIME
> AND F0.attr_a=F1.attr_a
> AND F0.attr_b=F1.attr_b
> AND B0.attr_a=B1.attr_a);
Just glancing over this, I realized that this will perform slowly. If you
have indexes on sensible things (ts, ids, attrs, etc.), something like:
SELECT F0.id FROM Foo as F0 JOIN Bar as B0 ON (F0.id=B0.id)WHERE NOT EXISTS ( (SELECT * FROM
FooAS F1, Bar as B1 WHERE ts < CURRENT_TIME AND F0.attr_a=F1.attr_a
AND F0.attr_b=F1.attr_b AND B0.attr_a=B1.attr_a AND B1.ts > B0.ts)
should be equivalent (assuming NOT NULL data) and perform better.