Mysql: Order Results by number of matching rows in Second Table

I'm not sure t开发者_高级运维he best way to word this question so bear with me.

Table A has following columns:

• id
• name
• description

Table B has the following columns:

• id
• a_id(foreign key to Table A)
• ip_address
• date

Basically Table B contains a row for each time a user views a row from Table A.

My question is how do I sort Table A results, based on the number of matching rows in Table B.

i.e

SELECT * 
  FROM TableA 
ORDER BY (SELECT COUNT(*) 
            FROM TableB 
           where TableB.a_id = TableA.id)

Thank you!

    SELECT a.*
         , b.cnt
      FROM TableA a
   LEFT OUTER JOIN (SELECT a_id
                         , COUNT(*) cnt
                      FROM TableB b
                   GROUP BY a_id) b
   ON a.id = b.a_id
     ORDER BY b.cnt

Something like this could do the trick.

select a.id, a.name, a.description, count(b.id) as count 
   from TableA as a 
   inner join TableB as b on a.id = b.a_id 
   group by a.id, a.name, a.description order by count 

Your query already does what you want. You might want to add DESC if you want the rows with the largest number of rows first:

SELECT * FROM TableA
ORDER BY (SELECT COUNT(*) FROM TableB where TableB.a_id = TableA.id) DESC

SELECT a.id, a.name, a.description, count(b.id) FROM TableA a
    JOIN TableB b on b.a_id = a.id
GROUP BY a.id, a.name, a.description ORDER BY COUNT(b.id);

You can add an DESC to the ORDER BY, maybe that is what you need:

SELECT a.id, a.name, a.description, count(b.id) FROM TableA a
    JOIN TableB b on b.a_id = a.id
GROUP BY a.id, a.name, a.description ORDER BY COUNT(b.id) DESC;