Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 911EA477530
	for <pgsql-sql@postgresql.org>; Fri, 28 Jun 2002 10:43:40 -0400 (EDT)
Mailbox-Line: From rk73@sea.plala.or.jp  Fri Jun 28 10:43:40 2002
Received: from mps3.plala.or.jp (mpsb-nat18.plala.or.jp [202.212.115.65])
	by postgresql.org (Postfix) with ESMTP id 221E84772B2
	for <pgsql-sql@postgresql.org>; Fri, 28 Jun 2002 10:09:47 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.117.7]) by mps3.plala.or.jp with ESMTP
	id <20020628140949.WXSJ9425.mps3.plala.or.jp@[192.168.0.4]>;
	Fri, 28 Jun 2002 23:09:49 +0900
Date: Fri, 28 Jun 2002 23:10:27 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Masaru Sugawara <rk73@sea.plala.or.jp>
Subject: Re: Calculating with sql
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20020628015018.3840.RK73@sea.plala.or.jp>
References: <afdeiv.214.1@kaarsemaker.nb.nu>
	<20020628015018.3840.RK73@sea.plala.or.jp>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020628231008.99AA.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05
X-Spam-Status: No, hits=-1.0 required=5.0
	tests=IN_REP_TO,FROM_ENDS_IN_NUMS,DOUBLE_CAPSWORD,
	UPPERCASE_50_75,FROM_AND_TO_SAME version=2.30
X-Spam-Level: 
X-Archive-Number: 200206/393
X-Sequence-Number: 8358

On Fri, 28 Jun 2002 01:50:49 +0900
I <rk73@sea.plala.or.jp> wrote:

> SELECT
>      a1.ARTIST, a1.TITLE
> FROM 
>      (SELECT a0.DISCID, a0.ARTIST, a0.TITLE
>         FROM tracks AS t0, albums AS a0
>       WHERE t0.DISCID = a0.DISCID
>       GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH
>       HAVING  a0.LENGTH /COUNT(t0.TRACK)
>                   = (SELECT max(mx.average) 
>                        FROM (SELECT albums.DISCID,
>                                             albums.LENGTH /COUNT(tracks.TRACK)
>                                                     AS average
>                                        FROM tracks, albums
>                                      WHERE tracks.DISCID = albums.DISCID
>                                      GROUP BY albums.DISCID, albums.LENGTH
>                                   ) AS mx
>                      )
>     ) AS a1


I noticed that this query was better than the previous. 
Actually, it came to be nearly equal to your original.


SELECT a0.ARTIST, a0.TITLE
  FROM tracks AS t0, albums AS a0
 WHERE t0.DISCID = a0.DISCID
 GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH
HAVING  a0.LENGTH /COUNT(t0.TRACK)
              = (SELECT max(mx.average) 
                    FROM (SELECT albums.LENGTH /COUNT(tracks.TRACK)
                                                    AS average
                                 FROM tracks, albums
                               WHERE tracks.DISCID = albums.DISCID
                              GROUP BY albums.DISCID, albums.LENGTH
                               ) AS mx
                  );
;



Regards,
Masaru Sugawara