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 a0WHERE t0.DISCID = a0.DISCIDGROUP 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