From: Masaru Sugawara <rk73@echna.ne.jp>
To: pgsql-sql@postgresql.org
Subject: Re: challenging query
Date: Sat, 06 Oct 2001 18:54:21 +0900
Message-ID: <20011006181844.7E29.RK73@echna.ne.jp> (raw)
In-Reply-To: <200110051503.RAA01444@rodos>
References: <579d73bf.0110030933.22b6d76e@posting.google.com>
<200110051503.RAA01444@rodos>
On Fri, 05 Oct 2001 17:03:41 METDST
Haller Christoph wrote:
> >
> > Consider the following table:
> >
> > A B C D select?
> > -------------------------------
> > 1 FOO A1 100 n
> > 1 BAR Z2 100 n
> > 2 FOO A1 101 y
> > 2 BAR Z2 101 y
> > 3 FOO A1 102 y
> > 4 BAR Z2 99 y
> > 5 FOO A1 99 n
> > 6 BAR Z2 98 n
> > 7 FOO AB 103 y
> > 7 BAR ZY 103 y
> >
> > This table has the idea of "groups", that is, a group is defined as
> > all of the words from B that have the same number A. The values in
> > column C also matter- we want to select both groups A=7 and A=1 since
> > they contain different values C. Note that the groups defined by A=1
> > and A=3 are distinct- they do not contain the same number of words
> > from B, so we want to select them both. Also note that D is datetime,
> > and all the rows with the same number A will have the same D (this is
> > actually ensured by a single row in another table.)
> >
> > I want to select all of the numbers A which define distinct groups and
> > have the highest datetime D. Is this possible in a SQL query?
> >
> Now that I've read your request more attentively, I understand what
> you want. But I have to admit I have no idea how to word the query,
> I even don't know if it's possible at all.
> Regards, Christoph
>
I also haven't satisfactorily understood the mean of the epilogue,
but I probably think he wanted to account for the following table
which is separated into "groups".
A B C D select?
------------------------------------
1 FOO A1 100 n
1 BAR Z2 100 n
2 FOO A1 101 y
2 BAR Z2 101 y
---------------------------------
5 FOO A1 99 n
3 FOO A1 102 y
---------------------------------
6 BAR Z2 98 n
4 BAR Z2 99 y
---------------------------------
7 FOO AB 103 y
7 BAR ZY 103 y
for instance:
select u0.A, u0.B, u0.C, u0.D
from (select t0.*, t1.cnt
from (select a, count(*) as cnt
from test_table
group by a ) as t1
inner join test_table as t0
on(t0.a = t1.a)
) as u0
where not exists (select u1.*
from (select t0.*, t1.cnt
from (select a, count(*) as cnt
from test_table
group by a ) as t1
inner join test_table as t0
on(t0.a = t1.a)
) as u1
where u1.cnt = u0.cnt
and u1.a != u0.a
and u1.d > u0.d
and u1.b = u0.b
and u1.c = u0.c
)
;
a | b | c | d
---+-----+----+-----
2 | BAR | Z2 | 101
2 | FOO | A1 | 101
3 | FOO | A1 | 102
4 | BAR | Z2 | 99
7 | BAR | ZY | 105
7 | FOO | AB | 105
(6 rows)
Have a nice weekend!
----------------------
Masaru Sugawara
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: rk73@echna.ne.jp
Subject: Re: challenging query
In-Reply-To: <20011006181844.7E29.RK73@echna.ne.jp>
* 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