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

what version of postgres, newer ones handle null values slightly differently in queries, although there is a setting you can switch to make it work like the older versions. -mike ----- Original Message ----- From: "Milan Babuskov" <albis@eunet.yu> To: <php-db@lists.php.net> Sent: Thursday, June 19, 2003 10:10 AM Subject: Re: [PHP-DB] 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 > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php