Re: [PHP-DB] Re: order by an average in another table (basically a top 10 list)

[Michael Scappa]

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 [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
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


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: order by an average in another table (basically a top 10 list)

[chris]

On Wed, 18 Jun 2003 16:06:23 -0400, Chris [EMAIL PROTECTED] wrote:

I've done a google search, but most of the results are for the part I've 
already figured out.  People that have the top 10 sorting usually want 
money for their system (one wanted an absurd $500).

I'm working on a database application where users vote on how much they 
like a particular picture.  All of the picture information is in one 
table, and all of the votes are within another table.  The picture 
table's auto incrementing id is referenced in the ratings table.

Displaying the actual average rating of a particular photograph is easy 
enough:
SELECT avg(rating) FROM ratings WHERE parent_id = '$id'

But how the heck would one select the top $n entries, ordered by the 
average of the photo's ratings?  Ideally, I'd like the query to work in 
both MySQL and PostgreSQL.

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).

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php