SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: Order by rating
-
Jan 17, 2006, 13:50 #1
- Join Date
- Mar 2005
- Posts
- 78
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Order by rating
I have a table with five fields:
ITEMS
id
title
text
rating
numvotes
I want to order the items by their rating.
First approach:
Code:SELECT * FROM items ORDER BY rating DESC LIMIT 10;
Second approach:
Code:SELECT * FROM items ORDER BY rating DESC WHERE numvotes > 5 LIMIT 10;
However, I would like it that the following:
Code:rating: 4,3 (out of 5) numvotes 150
Code:rating 4,4 numvotes 10
How would I be able to do something like that?
-
Jan 17, 2006, 14:03 #2
- Join Date
- Oct 2003
- Location
- St. Catharines, ON Canada
- Posts
- 1,708
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 17, 2006, 14:07 #3
-
Jan 18, 2006, 01:53 #4
- Join Date
- Mar 2005
- Posts
- 78
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks