Order by rating

[bartb]

I have a table with five fields:

ITEMS
id
title
text
rating
numvotes

I want to order the items by their rating.

First approach:

SELECT * FROM items ORDER BY rating DESC LIMIT 10;

Not very good.

Second approach:

SELECT * FROM items ORDER BY rating DESC WHERE numvotes > 5 LIMIT 10;

Better.

However, I would like it that the following:

rating: 4,3   (out of 5)
numvotes 150

would score higher than

rating 4,4
numvotes 10

Since it has alot more votes and is thus a better representation.

How would I be able to do something like that?

[guelphdad]

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.

[guelphdad]

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.

[bartb]

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.