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:

PHP Code:

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

PHP Code:

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

PHP Code:

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