Thinking about that some more, the outer select should be unneeded. Just move the ORDER BY to the first subquery and you should be good to go. And add the missing FROM shows clause that I completely forgot.
select showId, showTitle ( select avg(rating) from airdates where showId = shows.showId ) as avgRating from shows order by avgRating which, aside from formatting differences, is exactly what John proposed yesterday. Note to self: never try and think code while doing drywall. ;) cheers, barneyb On 11/8/05, Barney Boisvert <[EMAIL PROTECTED]> wrote: > This is untested, but should work: > > select * > from ( > select shows.showId, shows.showTitle, ( > select avg(rating) > from airdates > where showId = shows.showId > ) as avgRating > ) t > order by avgRating > > cheers, > barneyb > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223645 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
