title: getting three records in descending order of each category using codeigniter
body: <p>I've two categories and I want to fetch three records of each category later I found this link <a href="https://stackoverflow.com/questions/2040655/union-query-with-codeigniters-active-record-pattern#answer-14658160">UNION query with codeigniter's active record pattern</a> after this I change my DB_Active_rec file and add this code also</p>
  
  <pre><code>var $unions = array();
  
  public function union_push($table = '') {
      if ($table != '') {
          $this-&gt;_track_aliases($table);
          $this-&gt;from($table);
      }
  
      $sql = $this-&gt;_compile_select();
  
      array_push($this-&gt;unions, $sql);
      $this-&gt;_reset_select();
  }
  
  public function union_flush() {
      $this-&gt;unions = array();
  }
  
  public function union() {
      $sql = '(' . implode(') union (', $this-&gt;unions) . ')';
      $result = $this-&gt;query($sql);
      $this-&gt;union_flush();
      return $result;
  }
  
  public function union_all() {
      $sql = '(' . implode(') union all (', $this-&gt;unions) . ')';
      $result = $this-&gt;query($sql);
      $this-&gt;union_flush();
      return $result;
  }
  </code></pre>
  
  <p>and then I create codeigniter's function based query like this</p>
  
  <pre><code>$this-&gt;db-&gt;select("*");
  $this-&gt;db-&gt;from("media m");
  $this-&gt;db-&gt;join("category c", "m.category_id=c.id", "INNER");
  $this-&gt;db-&gt;order_by("m.media_files", "DESC");
  $this-&gt;db-&gt;limit(3);
  $this-&gt;db-&gt;union_push();
  $this-&gt;db-&gt;select("*");
  $this-&gt;db-&gt;from("media m");
  $this-&gt;db-&gt;join("category c", "m.category_id=c.id", "INNER");
  $this-&gt;db-&gt;order_by("m.media_files", "DESC");
  $this-&gt;db-&gt;limit(3);
  $this-&gt;db-&gt;union_push();
  $getMedia = $this-&gt;db-&gt;union_all();
  </code></pre>
  
  <p>create this</p>
  
  <pre><code>(SELECT * FROM media m INNER JOIN category c ON 
  m.category_id = c.id ORDER BY m.media_files DESC LIMIT 3)
  UNION ALL
  (SELECT * FROM media m INNER JOIN category c ON 
  m.category_id = c.id ORDER BY m.media_files DESC LIMIT 3)
  </code></pre>
  
  <p>Now it is fetching records but not properly I want to use only query, it showing six records first query fetch 3 records and second query fetch three records now records are duplicate I check the id of records it is 6,5,4 and again 6,5,4. It can be done also by <code>PHP</code> but I want to use query. Thanks in advance</p>