如何将recipe_id不同的所有评级列值相加

Question

To be more specific I have a table with recipes. In another table I keep ratings associated with these recipes. Each rating has an unique id, the second column is the recipe's id, the third column is the rating (from 1-5).

Expected result is the recipe_id, where all ratings associated with that recipe_id are the highest.

I hope you understand, I have no idea how to approach such query properly.

Hopefully I've posted my question properly, it's my first one.

Answer 1

You could use a self-join:

SELECT
  r.id,
  r.recipe_id,
  r.rating
FROM
  recipes_ratings r LEFT JOIN recipes_ratings r2
  ON r.recipe_id = r2.recipe_id
     AND r2.rating>r.rating
WHERE
  r2.rating IS NULL

Please see a fiddle here.