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

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

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

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

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

you can try this method:

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:

I modified the query to look like:
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.

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