Re: challenging query

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