Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9UHvNP94256 for ; Tue, 30 Oct 2001 12:57:23 -0500 (EST) (envelope-from rk73@echna.ne.jp) Received: from VIP333 [210.175.55.212] by echna.ne.jp with ESMTP (SMTPD32-6.06) id A9FA396A01B0; Wed, 31 Oct 2001 02:57:14 +0900 Date: Wed, 31 Oct 2001 02:57:39 +0900 From: Masaru Sugawara To: pgsql-sql@postgresql.org Subject: Re: challenging query In-Reply-To: <20011006181844.7E29.RK73@echna.ne.jp> References: <200110051503.RAA01444@rodos> <20011006181844.7E29.RK73@echna.ne.jp> Errors-To: Message-Id: <20011031025141.402B.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.06 X-Archive-Number: 200110/417 X-Sequence-Number: 5368 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