[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] challenging query
From:       Masaru Sugawara <rk73 () echna ! ne ! jp>
Date:       2001-10-30 17:57:39
[Download RAW message or body]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[prev in list] [next in list] [prev in thread] [next in thread]