Message-ID: <3CE40413.8050101@wildbrain.com>
Date: Thu, 16 May 2002 12:10:11 -0700
From: Gregory Brauer <greg@wildbrain.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0rc2) Gecko/20020510
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SQL over my head...
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


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