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

Question

I'm not sure the 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!

Answer 1

    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

Answer 2

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 

Answer 3

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

Answer 4

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;