title: PHP MySQL foreign key sorting
body: <p>I have two tables, one is "Posts" and other is "Comments" that have foreign key from Posts.
  How to display Posts by most Comments</p>
  
  <p>Post Table</p>
  
  <pre><code>+----+-------+--------------+
  | Id | Name  | Message      |
  +====+=======+==============+
  | 1  | John  | John's msg   |
  | 2  | Marco | Marco's msg  |
  | 3  | Ivan  | Ivan's msg   |
  +----+-------+--------------+
  </code></pre>
  
  <p>Comment table, PostId is foreign key</p>
  
  <pre><code>+----+-------+--------------+--------+
  | Id | Name  | Comment      | PostId |
  +====+=======+==============+========+
  | 1  | John  | John's msg   | 2      |
  | 2  |Joseph |Joseph's msg  | 2      |
  | 3  | Ivan  | Ivan's msg   | 2      |
  | 4  |Natalie|Natalie's msg | 1      |
  +----+-------+--------------+--------+
  </code></pre>
  
  <p>On frontend page Posts would be sorted by id 2 as it have most comments, then 1, and post id 2 as last.</p>
  
  <p><strong>Solution thx to GurV:</strong></p>
  
  <pre><code>select p.*,
      (
          select count(*) cnt
          from comments c
          where c.postid = p.id
          ) cnt
  from posts p
  order by cnt desc;
  </code></pre>