I need some help with the mySQL SORT statement for listing reviews by
how helpful they were rated by users.

I have two columns in the review table: helpfulrate and totalrate.
helpfulrate contains the number of people that rated the review
helpful and totalrate contains the total number of people that rated
the review. I use the two columns to get "XX out of XX people found
this review helpful".

Right now I am using the following text for sorting the reviews by
which is most helpful:
"... ORDER by IFNULL(helpfulrate/totalrate,0) DESC,totalrate DESC" 

The above code orders by the highest percentage first (of people who
rated the review helpful divided by the total number of people that
rated the review), then orders by the total number of people that
rated the review.

So 50 out of 50 people would be higher than 1 out of 1 or 2 out of 2.


The problem is, why should a review that is only 1 out of 1 be ranked
higher than a review that is 250 out of 251. I know that it shouldn't.

I have also tried sorting in this manner "(HELPFUL / TOTAL) *
HELPFUL", but it is doesn't sort well in certain situations.


Amazon.com has some way for sorting well, but I just can't figure out
a good way to do it. Here is an example of how they sort:
http://www.amazon.com/exec/obidos/tg/stores/detail/-/books/0439434866/customer-reviews/ref=cm_rev_sort/103-1356278-6719830?show=-votes&Go.x=5&Go.y=19

PLEASE only answer this question if you know of a way to sort with
mySQL that is VERY, VERY similar to amazon.com. All I need is the sort
part, the rest of the select statement is working flawlessly.

Hi,

Looking at your example, when you select the sort method "Most Helpful
First", it is obvious that Amazon sorts the reviews only by the number
of "helpful" votes. It only takes the number of total votes into
acount when the helpful votes are equal.

At the time I looked at the page, the ordering was like:

1. 63 of 72
2. 58 of 71
3. 28 of 32
4. 25 of 39
5. 24 of 37
6. 24 of 32
7. 24 of 25
8. 22 of 29 
.
.
.

As you see in the 5th, 6th and 7th entries, when there is an equality
among the number of helpful votes, it sorts by total votes.

I actually skimmed all 235 reviews, and a few other items reviewed at
Amazon com. All of them supported the theory.

So your order statement would be:

... ORDER BY helpfulrate DESC, totalrate DESC

Please request further clarification if you have any questions.

Hope this helps
Regards
Bio
Google Answers Researcher

---

Request for Answer Clarification by werdup-ga on 19 Feb 2003 17:43 PST

Wow, your right. I didn't think it could be that simple. I don't think
thats the best way of doing it at all.

This is my first time using Google Answers, so I don't know if I can
do this, but...what about BizRate's method for sorting, shown here:
http://electronics.bizrate.com/marketplace/product_info/reviews__cat_id--11140000,prod_id--6487970,name--Canon%20ZR-45MC%20%28%20ZR-45MC%20%29.html

I thought BizRate would be the same as Amazon.com (that is why I
didn't list it as well), but it isn't, as you can see at the link, and
below:
66 / 67
26 / 27
31 / 34
15 / 18
13 / 16
15 / 19
5 / 6
2 / 2
2 / 2
4 / 6
1 / 3
8 / 43
2 / 29

What I like about BizRate's method much more, is that a review that is
"5 / 6" is ranked higher than a review that is "8 / 43" OR a review
that is "1 / 1" is higher than a review that is "2 / 29". That makes
more sense right?

---

Clarification of Answer by bio-ga on 19 Feb 2003 18:09 PST

Since this clarification request is beyond the scope of the original
question, you have a good chance to get a more thorough answer from a
researcher if you post it as a new question. But I will try to comment
on it as far as I understand:

First, I think BizRate's default sort method is NOT "most helpful
first". How did you get that impression?

For example, you require in your example that "1 / 1" is higher than a
review that is "2 / 29", but in the example page at BizRate, 15/18 is
above 5/6. This is inconsistent with your requirement.

Actually your example ("5 / 6" is ranked higher than a review that is
"8 / 43" OR a review that is "1 / 1" is higher than a review that is
"2 / 29") can be satisfied with the first method you suggested in your
original question (helpfulrate/totalrate). Why don't you stick with
your original method?

Now if you ask for my personal opinion, Amazon's method is more
logical as far as database server's CPU time is concerned. Their
method is the faster because:

1. Operation does not require any mathematical evaluations
2. The two columns (helpfulrate and totalrate) can be indexed so that
the data is already sorted, and no further operation is needed.

Hope this clarifies the answer
Regards
Bio
Google Answers Researcher

---

Request for Answer Clarification by werdup-ga on 19 Feb 2003 19:03 PST

Thank you very much!

---

Clarification of Answer by bio-ga on 19 Feb 2003 20:09 PST

Thank you very much for your rating and the generous tip. I wish I
could be helpful.

Hoping to see you again.

Bio