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