Egad! How does one fetch ordered results from this query?

[Velocd]

I have what at first seemed to be a simple task, but now after countless attempts .. I can't figure it out. :speechless:


$sql = 'SELECT entryid, avgrating, num_ratings FROM entries';


Let's say I have these 3 entries in the table `entries` :


entryid | avgrating | num_ratings
-------------------------------------------
1 1 3000
2 9 500
3 9 1


I would like to somehow by modifying the query above, to return the results like this:


entryid | avgrating | num_ratings
-------------------------------------------
2 9 500
1 1 3000
3 9 1


Basically this is ordering by the maximum avgrating and the maximum num_ratings , with some comparison in between though. For example, entryid 3 and its avgrating of 9 is larger than avgrating 1, but technically since entryid 1 has 3,000 ratings to it, it could have had 60 votes that were all 9's.

Hopefully this is making some logic. I can't just do a simple query and order it by avgrating , because an entry with an avgrating of 10 and one vote would display above an entry with an avgrating of 9 and 500 votes.

I can't order by num_ratings , because an entry, like entryid 1 in the example, would appear above an entry like entryid 2.

The solution is probably something simple I've just overlooked, as usual.. but I need to get it done in one query (if possible), and not have to make 2 queries with extra PHP code in between. Help would be much appreciated. ;)

[Xenon]

you can try this method:

$sql = 'SELECT entryid, avgrating, num_ratings, avgrating*num_ratings AS ordervalue FROM entries ORDER BY ordervalue';

[Velocd]

Thanks xenon, the method in theory is correct.. don't know why it didn't occur to me before, but a more complex algorithm is still needed... for if I had:

1 Avgrating - 4800 Results (product of 4800)
9 Avgrating - 500 Results (product of 4500)

:dead:

[Velocd]

I modified the query to look like:
SELECT *,((avgrating*num_ratings)*(avgrating/2)) AS score FROM entries ORDER BY score

And this seems to fix the problem I posted above... not sure if it is the best solution (or if the math is even correct), but.. for now it'll do.

[Xenon]

you're welcome :)

i just wanted to give the idea, the algorithm depends on how strong which value should be, so i couldn't help further :)