Listing most commented news

I have two tables in my database.

• Comments
  • CommentsID
  • MembersID
  • CommentsBy
  • CommentsDesc
  • CommentsActive
  • CommentsDateEntered
  • NewsID

• News
  • NewsID
  • MembersID
  • CategoriesID
  • ImagesID
  • NewsTitle
  • NewsShortDesc
  • NewsDesc
  • NewsActive

I need to take top 5 commented news (active comments and ac开发者_开发知识库tive news) and list their titles using one query. I hope I made sense. But I am really confused here. Any suggestion appreciated.

Is this what you want?

SELECT TOP(5) News.* 
FROM News 
WHERE News.NewsActive = 1 
ORDER BY 
    (SELECT COUNT(*) 
     FROM Comments 
     WHERE Comments.NewsId = News.NewsId AND Comments.CommentsActive = 1) DESC;

Response to your comment would be something like:

SELECT TOP(5) News.*, 
    (SELECT COUNT(*) 
     FROM Comments 
     WHERE Comments.NewsId = News.NewsId AND Comments.CommentsActive = 1) AS TotalComments
FROM News 
WHERE News.NewsActive = 1 
ORDER BY TotalComments DESC;