Q:

Order using count from another table

Hi I need to Order using count from another table. i found this great example, im using it as model for a query i need. SQL - How To Order Using Count From Another Table
The model im using for query is: 
SELECT bloggers.*, COUNT(post_id) AS post_count
    FROM bloggers LEFT JOIN blogger_posts 
    ON bloggers.blogger_id = blogger_posts.blogger_id
    GROUP BY bloggers.blogger_id
    ORDER BY post_count

But i have a syntax problem in mine, i guess, im trying to replace the next query, with the one that counts another table... but i cant manage to do it. Original query:
$res3=$db->execute_query("select id,scode,sname from ".TABLE_PREFIX."states where ccode=? order by sname asc",array($country));

Trying to replace with this query..
 $res3=$db->execute_query("select ".TABLE_PREFIX."states.* , COUNT(".TABLE_PREFIX."items.state) AS state_count FROM ".TABLE_PREFIX."states LEFT JOIN ".TABLE_PREFIX."items ON ".TABLE_PREFIX."states.id = ".TABLE_PREFIX."items.state GROUP BY ".TABLE_PREFIX."states.id  ORDER BY state_count DESC",array($country));

A:

Try this:
$res3=$db->execute_query("select a.* , COUNT(b.state) AS state_count FROM states a 
LEFT JOIN items b ON a.id = b.state 
GROUP BY a.id 
ORDER BY state_count DESC",array($country));

