From: Joel Burton <joel@joelburton.com>
To: Gregory Brauer <greg@wildbrain.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL over my head...
Date: Thu, 16 May 2002 16:14:05 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNKEHJCOAA.joel@joelburton.com> (raw)
In-Reply-To: <3CE40413.8050101@wildbrain.com>
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@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
>
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: joel@joelburton.com, greg@wildbrain.com
Subject: Re: SQL over my head...
In-Reply-To: <JGEPJNMCKODMDHGOBKDNKEHJCOAA.joel@joelburton.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