CODEWITHSUNDEEP
asp-classic
zurna
zurna

Reputation: 1191

Listing most commented news

I have two tables in my database.

I need to take top 5 commented news (active comments and active news) and list their titles using one query. I hope I made sense. But I am really confused here. Any suggestion appreciated.

Upvotes: 0

Views: 57

Answers (1)

dale
dale

Reputation: 758

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;

Upvotes: 1

Related Questions