pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@echna.ne.jp>
To: pgsql-sql@postgresql.org
Subject: Re: challenging query
Date: Wed, 31 Oct 2001 02:57:39 +0900
Message-ID: <20011031025141.402B.RK73@echna.ne.jp> (raw)
In-Reply-To: <20011006181844.7E29.RK73@echna.ne.jp>
References: <200110051503.RAA01444@rodos>
	<20011006181844.7E29.RK73@echna.ne.jp>

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




view thread (5+ messages)

Message-ID: <20011031025141.402B.RK73@echna.ne.jp>
Permalink:  ../20011031025141.402B.RK73@echna.ne.jp/
Also on:    postgresql.org/message-id/20011031025141.402B.RK73@echna.ne.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@echna.ne.jp
  Subject: Re: challenging query
  In-Reply-To: <20011031025141.402B.RK73@echna.ne.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox