Re: 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);

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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Gregory Brauer
> Sent: Thursday, May 16, 2002 3:10 PM
> To: pgsql-sql(at)postgresql(dot)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(at)wildbrain(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>