Re: challenging query

On Sat, 06 Oct 2001 18:54:21 +0900
Masaru Sugawara wrote:

>
> 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
>
>
> 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
> )
> ;

I noticed there were two vain subselects in the query
when I had checked past queries by an EXPLAIN, and
gave a small change to the query.

-- on 7.1.2
select u0.*, u1.cnt
from (select a, count(*) as cnt
from test_table group by a ) as u1
inner join test_table as u0 on(u0.a = u1.a)
where not exists (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)
where t1.cnt = u1.cnt
and t0.a != u0.a
and t0.d > u0.d
and t0.b = u0.b
and t0.c = u0.c
)
;

Masaru Sugawara