CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 62366

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.

Upvotes: 0

Views: 1206

Answers (5)

David Robertson
David Robertson

Reputation: 499

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

Upvotes: 0

Sven Lilienthal
Sven Lilienthal

Reputation: 6484

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      |
=================================

Upvotes: 1

Andomar
Andomar

Reputation: 238078

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.

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60498

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

Upvotes: 0

Ian P
Ian P

Reputation: 12993

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.

Upvotes: 0

Related Questions