getting three records in descending order of each

I've two categories and I want to fetch three records of each category later I found this link UNION query with codeigniter's active record pattern after this I change my DB_Active_rec file and add this code also

var $unions = array();

public function union_push($table = '') {
    if ($table != '') {
        $this->_track_aliases($table);
        $this->from($table);
    }

    $sql = $this->_compile_select();

    array_push($this->unions, $sql);
    $this->_reset_select();
}

public function union_flush() {
    $this->unions = array();
}

public function union() {
    $sql = '(' . implode(') union (', $this->unions) . ')';
    $result = $this->query($sql);
    $this->union_flush();
    return $result;
}

public function union_all() {
    $sql = '(' . implode(') union all (', $this->unions) . ')';
    $result = $this->query($sql);
    $this->union_flush();
    return $result;
}

and then I create codeigniter's function based query like this

$this->db->select("*");
$this->db->from("media m");
$this->db->join("category c", "m.category_id=c.id", "INNER");
$this->db->order_by("m.media_files", "DESC");
$this->db->limit(3);
$this->db->union_push();
$this->db->select("*");
$this->db->from("media m");
$this->db->join("category c", "m.category_id=c.id", "INNER");
$this->db->order_by("m.media_files", "DESC");
$this->db->limit(3);
$this->db->union_push();
$getMedia = $this->db->union_all();

create this

(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)

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 PHP but I want to use query. Thanks in advance

I dont know code-igniter, but basicly you want it to do the union first and then apply the order by over the whole set. This would require a subquery. It should result in the following SQL query:

select * from
    ((SELECT * FROM media m INNER JOIN category c ON m.category_id = c.id )
    UNION ALL
    (SELECT * FROM media m INNER JOIN category c ON m.category_id = c.id)) T
ORDER BY m.media_files DESC LIMIT 3

Hope it helps you some.