Hello All, I need to find a function. the scenario is : I have 2 table. 1. news table 2. news_comment table
need to find the most commented 20 news. the news_comment table has the relationship with the news table
Thank you
+1
A:
try the give query. it may help.
Top 20 give first 20, join lin 2 tables count(news_comment.id) give no of comment
select top 20 news.*, count(news_comment.id) as no_of_comment from news inner join news_comment on news.id = news_comment.newsid order by no_of_comment desc
KoolKabin
2010-06-13 13:42:42
but i need the sql in mySql format, its showing syntax error
santanu
2010-06-13 13:47:46
Possibly a GROUP BY needed. Which DB engine are you working with? Just adapt the given query.
Federico Cristina
2010-06-13 13:51:01
for mysql: select a.id, a.title, count(b.id) no_of_comment from news a inner join news_comment b on a.id = b.news_id group by a.id, a.title order by no_of_comment limit 0,20
KoolKabin
2010-06-13 16:29:26