Sorting records after a count()

[skyer2000]

I'm pretty sure this is suitable for the PHP forum as the solution is probably going to require some PHP code.

 

What I have right now is a query that pulls results from a database and orders them by "date, priority, status". These are parent records, which can have a certain amount of child records underneath them. So for example, one record might have 3 child records, and I also need it to sort by that.

 

Since getting the number of child records requires another count() query, how would I go about sorting by which record has the most child records?

[Barand]

SELECT p.date, p.priority, p.status, COUNT(c.childID) as children

FROM mytable p  LEFT JOIN childtable c ON p.parentid = c.parentid

GROUP BY p.date, p.priority, p.status

ORDER BY children DESC