title: Count, group by, order by in one query using Active record Class in CodeIgniter?
body: <p>I am using CodeIgniter and I have 2 tables in my database.</p>
  
  <p>1st called <strong>continents</strong> and looks like this:</p>
  
  <pre><code>    id     | continent_name
      -----------------------
      1      | Africa
      2      | Antarctica
      3      | Asia
      4      | Australia
      5      | Europe
      6      | North America
      7      | South America
  </code></pre>
  
  <p>2nd called <strong>states</strong> and looks like this:</p>
  
  <pre><code>        id | continent_id  | country_name
          -----------------------------------
          1  | 5             | Denmark
          2  | 1             | Angola
          3  | 7             | Peru
          4  | 5             | Germany
          5  | 7             | Venezuela
          6  | 1             | Egypt
          7  | 5             | Spain
          8  | 5             | France
          9  | 7             | Argentina
          10 | 6             | Canada
  </code></pre>
  
  <p>And now I want an ordered list grouped by continent_id and oredered from most records (in my case Europe) to the least (Asia) result like this:</p>
  
  <pre><code>Europe (4)
  South America (3)
  Africa (2)
  North America (1)
  Asia (0)
  </code></pre>
  
  <p>The number of records is not important I will be using another foreach loop inside. But I need somehow achieve the count, group and order active record functions in one query:</p>
  
  <p>So far I have this in my model:</p>
  
  <pre><code>function get_top_continents() {
  
              $q = $this-&gt;db-&gt;from('states')
                            -&gt;join('continents', 'states.continent_id = continents.id')
                            -&gt;group_by('continent_id')
                            -&gt;get();
              return $q;
  
  
      }
  </code></pre>
  
  <p>But I need to order the result also on the number of items in group. </p>
  
  <p>How to do such <strong>order by</strong> condition?</p>