I need help with a sql command... to count and then order by that count

[turkman]

Hey im creating a forum and i want to list the most popular topics on the page, i was going to sort it by replies however i have a problem... in my table there is no running total of replies, there is only a new entry for each reply - so if i want to get the total replies for a thread id do select * from table_name where id = 'id'  then id do a mysql_num_rows() and get the replies

 

however when i want to show the number of replies and sort them by the most replied this becomes a problem... is there a way of doing this in sql?

[Barand]

SELECT t.threadid, COUNT(r.replyid) as totreply
FROM thread t
LEFT JOIN reply r USING (threadid)
GROUP BY t.threadid
ORDER BY totreply DESC

[turkman]

thanks.. thats gave me a basic overview... one difference though my topics and replies are in the same table... i just have a replytoo column 0 = new thread - anything over 0 is the thread id its replying too.

 

would that be

 

SELECT table_name.id, COUNT(table_name.replytoo) as totreply
FROM table_name
WHERE table_name.replytoo > 0
GROUP BY table_name.id
ORDER BY totreply DESC

[Barand]

Use a self join

SELECT a.id, COUNT(b.replytoo) as totreply
FROM tablename a
LEFT JOIN tablename b ON a.id = b.replytoo
GROUP BY a.id
ORDER BY totreply DESC