MySQL select highest rated

I have 2 columns, one called rating_average and one called rating_count.

I need to select the 3 rows with the highest rating with the rating count factored into 开发者_开发问答the equation as well.

You can do math in the order by clause, like:

select    *
from      YourTable
order by  rating_average * rating_count DESC
limit     3

In MySQL, you can add limit at the end of a query to restrict the rowset to the first N rows.

If you'd give an example, we could probably provide you with a more specific solution.

SELECT *
FROM table
ORDER BY rating_average DESC,
         rating_count DESC
LIMIT 3

That gives the first 3 rows, sorted first by rating_average and then by rating_count.

Example:

=================================
| rating_average | rating_count |
=================================
|       9.1      |       5      |
|       8.9      |       9      |
|       8.9      |       3      |
=================================

SELECT * FROM table ORDER BY rating_average DESC LIMIT 3

That may work, but you should post the schema so we can see exactly what you want.

How do you want to factor in the rating count exactly? If you wanted to limit it to say items with at least 3 ratings, you could do this:

SELECT rating_average, rating_count
FROM mytable
WHERE rating_count > 3
ORDER BY rating_average DESC
LIMIT 3

If you don't have rating_average and rating_count to start with but you would still like to have the results as given by Andomar, here is a complete query using only the tables 'products' and 'reviews'

    SELECT products.title,reviews.fk_productid,
    COUNT(reviews.review_id) AS review_count,
    AVG(reviews.rating) AS rating_average,
    COUNT(reviews.review_id) * AVG(reviews.rating) AS total_score 
    FROM reviews
    INNER JOIN products
    ON products.product_id = reviews.fk_productid
    GROUP BY fk_productid
    ORDER BY total_score DESC