need a mysql query that order by count

Hey
here are 2 mysql table

table1:
=======

id===name===desc
1 ===mike ===blah blah
2 ===nike ===blah blah
3 ===kike ===blah blah
4 ===fike ===blah blah


table2:
======

repID===id====reply
1 === 3====blah blah
2 === 1====blah blah
3 === 3====blah blah
4 === 2====blah blah
5 === 4====blah blah
6 === 1====blah blah
7 === 2====blah blah
8 === 2====blah blah


so, see? in table2...two replies for id1, three replies for id2, two replies for id3, one for id4


I want a mysql query that read data from table1 & show result ORDER BY reply count from table2.

plz help me

 

Do all ids in table1 have at least 1 reply in table2? If so, try

SELECT 
    table1.id
  , table1.name
  , table1.desc
  , COUNT(*) AS reply_count
FROM table1
INNER JOIN table2
ON table1.id = table2.id
GROUP BY 
    table1.id
  , table1.name
  , table1.desc
ORDER BY 4

Code (markup):

 

Simplest way is to do this

SELECT
*, (
	SELECT COUNT(
		`id`
	)
	 FROM
		`table2`
	WHERE
		`table1`.`id` = `table2`.`id`
	GROUP BY
		`id`
) 
AS
	`total`
FROM
	`table1`
ORDER BY
        `total`
DESC

Code (markup):

 

This would be the output

 

tnx CreativeClans & JAY6390
but which one is faster ??

 

There's nothing in it really. they both average 0.053 seconds

 

Jay, you count the time the query need to access..
good job..

 

Yup. Navicat is very useful