From: Gregory Brauer <greg@wildbrain.com>
To: pgsql-sql@postgresql.org
Subject: SQL over my head...
Date: Thu, 16 May 2002 12:10:11 -0700
Message-ID: <3CE40413.8050101@wildbrain.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@wildbrain.com
Subject: Re: SQL over my head...
In-Reply-To: <3CE40413.8050101@wildbrain.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox