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