Have Geek Brain

Mysql: order of results by number of matching rows in second table

I am not sure the best way to bring this issue forward is so impatient.

Table A has the 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 every time the user views a row from table A.

My question is how to sort the results of table A 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)

Thanks!

+2


a source to share


4 answers 


    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


+3


a source


Something like this might 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
0


a source


Your request already does what you want. You might want to add DESC if you want the rows with the most rows to come first:

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


a source 


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 DESC to ORDER BY, maybe this 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;
0


a source






More articles:


All Articles