Little help on a query..!

[iranor]

Heya, I have two tables.

The first one: articles, with the fields ArticleID, Title, Content.

The second one: comments, with the fields EntryID, ArticleID, Rank.

What I want to do is select all the articles from the table and order them by rank (which is, the sum of all "Rank" fields divided by the number of comments).

Example:
Table comments
EntryID, ArticleID, Rank
1, 1, 3
2, 2, 5
3, 2, 3

Table articles:
ArticleID, Title, Content
1, Test, Somecontent
2, Another test, Another content

Result: the article #2 would be first with a ranking of 4 while article 1 has a ranking of 3.

Basically what I need to do is sorting the table articles based on the ranking which is in the comments table

Any idea on how to do such query?

 

[ethorn10]

Somebody can probably make this better, but a starting point might be:

select a.ArticleID
   , a.Title
   , a.Content
   , sum(b.Rank)/count(*) as MyRanking
from articles a inner join
   comments b on a.ArticleID = b.ArticleID
group by a.ArticleID
   , a.Title
   , a.Content
order by 4 desc

Any additional fields you add to the select clause would also need to go in your group by clause and the number in the order by clause would need to match the placing of "MyRanking" in the select.

 

[r937]

any time you have a large column in a GROUP BY, is a hint to "push down" the grouping into a subquery

SELECT a.ArticleID
     , a.Title
     , a.Content
     , c.MyRanking
  FROM articles AS a 
INNER 
  JOIN ( SELECT ArticleID
              , SUM(Rank)/COUNT(*) AS MyRanking
           FROM comments 
         GROUP
             BY ArticleID ) AS c
    on c.ArticleID = a.ArticleID
ORDER 
    BY MyRanking DESC

r937.com | rudy.ca