From: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
---|---|
To: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Calculating with sql |
Date: | 2002-06-28 14:10:27 |
Message-ID: | 20020628231008.99AA.RK73@sea.plala.or.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, 28 Jun 2002 01:50:49 +0900
I <rk73(at)sea(dot)plala(dot)or(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jie Liang | 2002-06-28 15:26:43 | Re: Function question |
Previous Message | Steve Brett | 2002-06-28 13:52:48 | Re: Subtraction of Dates |