SQL: Find top-rated article in each category

I have a table articles, with fields id, rating (an integer from 1-10), and category_id (an integer representing to which category it belongs).

How can I, in one MySQL query, find the single article with the highest rating from each category? ORDER BY and LIMIT would usually be how I would find the top-rated article, I suppose, but I'm not sure how to mix that with grouping to get the desired result, if I even can. (A dependent subquery would likely be an easy answer, but ewwww. Is there something better?)

For the开发者_如何学编程 following data:

id | category_id | rating
---+-------------+-------
 1 |           1 |     10
 2 |           1 |      8
 3 |           2 |      7
 4 |           3 |      5
 5 |           3 |      2
 6 |           3 |      6

I would like the following to be returned:

id | category_id | rating
---+-------------+-------
 1 |           1 |     10
 3 |           2 |      7
 6 |           3 |      6

---

Try These

SELECT id, category_id, rating
FROM   articles a1
WHERE rating = 
(SELECT MAX(a2.rating) FROM articles a2 WHERE a1.category_id = a2.category_id)

OR

SELECT * FROM (SELECT * FROM articles ORDER BY rating DESC) AS a1 GROUP BY a1.rating;

---

You can use a subselect as the target of a FROM clause, too, which reads funny but makes for a slightly easier-to-understand query.

SELECT a1.id, a1.category_id, a1.rating 
FROM articles as a1, 
     (SELECT category_id, max(rating) AS mrating FROM articles AS a2 
      GROUP BY a2.category_id) AS a_inner 
WHERE 
  a_inner.category_id = a1.category_id AND 
  a_inner.mrating = a1.rating;