sql - Top articles with unique name -

sql - Top articles with unique name -

i need select top rated articles uniqune name. i've got 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 grouping articles name, counts average rating of articles same name.

select article.name, avg(vote.rating) rating article left bring together vote on vote.article_id = article.id grouping article.name

but want average rating of top rated articles. know how this? give thanks advice.

edit: want result:

name average_rating -------------------------- article 1 2.5 article 2 1

this lookig for:

select result.id, result.name, result.rating ( select article.id id, article.name name, avg(vote.rating) rating article left bring together vote on vote.article_id = article.id grouping article.id order rating desc ) result grouping result.name

probably, can handled improve works. give thanks helping.

sql dql