SQL Query to select top rating

[burhankhan]

Hi:

 

I tried very much, but not able to write query to select article, which have top rating.

 

Here is structure of tables:

 

ARTICLE
----------------------
id   -    title
1    -    that is first title
2    -    that is second title
3    -    that is third title
4    -    that is forth title
----------------------

Rating
-----------------------
articleid   -   rating
1           -    4
1           -    2
1           -    3
2           -    2
2           -    3
3           -    1
-----------------------

 

That is structure of two tables. I have to select articles, that have top average rating. (means avg(rating) )

 

I tried some quires but not able to get correct result.

 

Can any body help me?

 

Thanks

Burhan Khan

[hvle]

You should post this in mysql forum.

try something like this, I havent test it out but I think it will work

 

$sql = "select article.id, article.title, avg(rating.rating) as rating from article
left join rating on article.id =rating.articleid 
group by article.id 
order by rating desc;"

provide that your article table name is 'article' and your rating table name is 'rating'.

 

the query above will also select the article with no ratings, if you like to select only those with rating, use Join instead of left join.

[burhankhan]

Ohh sorry my mistake

 

And thanks for your help.

Its working now