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
