CODEWITHSUNDEEP
mysqlsql
Lina
Lina

Reputation: 627

SQL query - how to get item with highest rating?

I have two sql tables:

item
id  name   price
1   name1  100
2   name2  100
3   name3  100
4   name4  100

rates
id item_id rating
1  1       5
2  4       4
3  2       5
4  2       3
5  3       1
...

And I want to show 3 items with highest average rating. How should I do that?

I get average item rating like that:

$item =  mysql_query("SELECT * FROM item");
while($row = mysql_fetch_assoc($item)) {
    $id = $row['id'];
    $rate= mysql_query("SELECT AVG(rating) FROM rates WHERE id= $id");
}

Upvotes: 0

Views: 2279

Answers (2)

mu is too short
mu is too short

Reputation: 434665

You could compute the highest averages in a derived table and join to that:

select i.id, i.name, i.price
from item i
join (
    select id, avg(rating) as ar
    from rates
    group by id
    order by ar desc, id
    limit 3
) dt on i.id = dt.id;

I added id to the ORDER BY to force consistent results in cases where you have duplicates.

Upvotes: 4

Kai Qing
Kai Qing

Reputation: 18833

$item =  mysql_query("SELECT i.*, (SELECT AVG(`rating`) FROM `rates` WHERE `item_id` = i.`id`) AS `rating` FROM `item` i ORDER BY `rating` DESC LIMIT 3");

Have you tried running a subquery like so? I don't think this would be the most efficient or accurate way to do this but it might give a basic result you may find useful.

Ideally you would process this on the back end to eliminate gaming (outliers in the standard deviation) and keep a constant average stored for each item, but that may be too complex for what you may need.

Upvotes: 1

Related Questions