Top articles with unique name

Question

I need to select top rated articles with uniqune name. I've got this articles table:

id     name   
---------------
1    Article 1 
2    Article 1
3    Article 2

and votes table:

article_id   rating
-------------------- 
   1           3
   1           2
   3           1

When I group articles by name, it counts average from rating even of articles with same name.

SELECT article.name, AVG(vote.rating) rating
FROM article
LEFT JOIN vote ON vote.article_id = article.id
GROUP BY article.name 

but I want to get average rating of just top rated articles. Does anyone know how to do this? Thank you for any advice.

EDIT: I want to get this result:

    name       average_rating
   --------------------------
   Article 1   2.5
   Article 2   1

Answer 1

This is what I was lookig for:

SELECT result.id, result.name, result.rating
FROM (
    SELECT article.id AS id, article.name AS name, AVG(vote.rating) AS rating
    FROM article 
    LEFT JOIN vote ON vote.article_id = article.id
    GROUP BY article.id
    ORDER BY rating DESC
) AS result
GROUP BY result.name

Probably, it can be handled better but this just works. Thank you for your helping.