Re: Re: order by an average in another table (basically a "top 10" list)

chris wrote:

I managed to figure it out on my own. For those curious, here's a starting point: "SELECT parent_id, avg(rating) as average FROM ratings WHERE average IS NOT NULL GROUP BY parent_id ORDER BY average DESC, parent_id DESC" It works in MySQL 3.x, but not certain about PostgreSQL. The not null check was necessary in my select statement, possibly due to the multiple joins I'm doing (I ended up with the oldest unrated picture at the top, then the top 9, instead of 10; the not null check eliminates this).

It's probably because NULLs are always on bottom (and on top if you use DESC). -- Milan Babuskov http://fbexport.sourceforge.net http://njam.sourceforge.net