From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Masaru Sugawara <rk73@sea.plala.or.jp>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calculating with sql
Date: Fri, 28 Jun 2002 23:10:27 +0900
Message-ID: <20020628231008.99AA.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <20020628015018.3840.RK73@sea.plala.or.jp>
References: <afdeiv.214.1@kaarsemaker.nb.nu>
<20020628015018.3840.RK73@sea.plala.or.jp>
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
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@sea.plala.or.jp
Subject: Re: Calculating with sql
In-Reply-To: <20020628231008.99AA.RK73@sea.plala.or.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