Hmmm... like perl, one can write SQL in dozens of ways. I would go like:

CREATE VIEW avgrating
 SELECT itemid, AVG( rating ) AS the_avg FROM ratinglist 
 WHERE catagory = ?
 GROUP BY itemid
 ORDER BY AVG( rating );
SELECT itemlist.itemid, the_avg, foo FROM itemlist, avgrating
 WHERE itemlist.itemid = avgrating.itemid;
[download]

I don't know what that does to performance, neither if mysql supports views. But I guess that the view creation allows the RDBM to improve efficiency of the second SELECT. Moreover, I have understood that the implicit JOINing allows for speed optimization in most RDBMs.

Hope this helps,

Jeroen
"We are not alone"(FZ)