Order by rating
I have a table with five fields:
I want to order the items by their rating.
Not very good.
SELECT * FROM items ORDER BY rating DESC LIMIT 10;
SELECT * FROM items ORDER BY rating DESC WHERE numvotes > 5 LIMIT 10;
However, I would like it that the following:
would score higher than
rating: 4,3 (out of 5)
Since it has alot more votes and is thus a better representation.
How would I be able to do something like that?
So what's better
4.2 with 25 votes or 4.3 with 23 votes?
What you will have to figure out is perhaps a weighted average maybe multiply your rating with your number of votes. only thing 4.2 with 50 votes would then be the same as 2.1 with 100 votes.
By the way your second approach above will only result in an error since you can't have a where clause after an order by clause.
as an aside, you should have two tables 1 with id, title, text the second table with id, rating
you then won't have duplication of information. you also won't have to update number of votes by hand and update your table, you can just let the database calculate that for you.
I think it's better if I combine that data in 1 table (I still have the second table, though with IP info and such)
Why? I get alot more views (SELECTS) than ratings (INSERT&UPDATE) which I think will make it faster if I can do it in 1 query.
Thanks for your insights.