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 querywhen I had checked past queries by an EXPLAIN, andgave a small
changeto 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_tableas u0 on(u0.a = u1.a) where not exists (select t0.*, t1.cnt from (select a, count(*)
ascnt from test_table group by a) as t1 inner join
test_tableas 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
