Re: SQL over my head... - Mailing list pgsql-sql

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
ASF1,                   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);
 

is what you want. If not, can you post a brief dump of INSERT statements
with sample data, and the output you're expecting?

If you want the combinations of f.attr_a, f.attr_b, and b.attr_a only for
naturally joining foo/bars, join the tables in the subquery with the same
JOIN ... as in the outer query. It's not clear from your question if you
want this or not.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Gregory Brauer
> Sent: Thursday, May 16, 2002 3:10 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] SQL over my head...
>
>
>
> I've got a rather difficult query that I'm hoping someone can help with.
>
> I have two tables, foo and bar.
>
> I'll just speak SQL...
>
> CREATE TABLE foo
>    (
>      id           serial primary key,
>      attr_a       int4 not null,
>      attr_b       int4 not null,
>      bar_id       int4 not null,
>
>      unique(attr_a , attr_b, bar_id),
>
>      foreign key(bar_id) references bar(id)
>    );
>
> CREATE TABLE bar
>    (
>      id           serial primary key,
>      attr_a       int4 not null,
>      ts           timestamp not null
>    );
>
>
> What I want to do is find all of the foo.id's where the
> foo.bar_id in that row points to a bar where the bar.ts
> is the most recent time that is before the current time
> among the sets of identical combiniations of foo.attr_a,
> foo.attr_b and bar.attr_a.
>
> Said another way...
>
> If I find the 3 item sets of foo.attr_a, foo._attr_b,
> and the bar.attr_a that a foo.bar_id points to, and sort
> them into groups where the the three values are the same,
> I want, as a result, one item from each of the groups
> where the time is the maximum of that group which is
> still earlier than the current time.
>
> Is this possible?  I'd appreciate any help anyone can give.
>
> Thanks.
>
> Greg Brauer
> greg@wildbrain.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>