Where did my orders on Amazon go?

mySQL Sort - Sort user reviews by helpfulness (like amazon.com)

• 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.

• Answer:

  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