In sport you have a countback rule when a competitors points are the same. So on your list you have 3 rated with 1, you'd then look back and only the first product had a 1 rating last month so it'd be the one you pick. You can't average a rating that is already an average so if you decided to count more than one month you'd have to query the raw data. ======================================== What's trickier is when you have a new product, or one with fewer sales, and you need to apply relevancy. Product A: 1000 sales, 750 ratings, $100 each, average 2.4 Product B: 100 sales, 50 ratings, $100 each, average 2.1 Product C: 100 sales, 90 ratings, $1000 each, average 2.5 Product C costs more and the buyers have been more motivated to leave a rating than both Products A and B. Does Product B deserve to be the top product when so few people buy it?
I am sorry. I think I asked my question in wrong way. What I actually want to do is to group by monthes and get the highest amount from average_rating column along with the productId currently I am using following query but It gives me wrong informations since it returns productId 1 for all monthes: select productid,max(average_rating),ratings_month from rating group by ratings_month. above query groups data buy monthes and get highest average_rating for each month but returns wrong productId. Do you know what I am doing wrong here?
aaah, gotcha, yep, that's not an easy query. I'd start with something like this https://www.db-fiddle.com/f/tLcy2nfqUdFizExhpxktRP/0 select ratings_month, max(average_rating) as best_rank from ratings group by ratings_month order by ratings_month Code (markup): and then go back and find the products that had that rating. It's not perfect but easier to maintain when you revisit the code and a few extra queries isn't the end of the world
There's a way, you can google for examples but I can't remember off the top of my head. I have that situation too and I have to hunt down the answer every time it pops up. If you're not confident with MySQL it can be better just to do it the long way.