how to use DISTINCT here in my query CODEIGNITER

问题:

$q = $this->db->select('
             books.title,
             reserved_books.id,
             reserved_books.isbn, 
             reserved_books.price,
             reserved_books.item_sold,
             reserved_books.date_sold,
             reserved_books.total_amount
            ')
        ->from('reserved_books')
        ->join('books','reserved_books.isbn= books.isbn')
        ->limit($limit,$offset);

how can i use distinct here in my query? reserved_books.isbn is the unique one.

回答1:

Try Below:

$this->db->distinct();

but Distinct will not always work. You should add ->group_by("name_of_the_column_which_needs_to_be unique");

$this->db->group_by('column_name');

回答2:

Adds the "DISTINCT" keyword to a query before Select

$this->db->distinct();

Refrence

回答3:

You don't need any join

 $query=$this->db->distinct()->select('table_attribute')->where('condition')->get('table_name');
  return $query->result();

回答4:

Here is a way to do this

$select =   array(
             'books.title',
             'reserved_books.id',
             'DISTINCT reserved_books.isbn', 
             'reserved_books.price',
             'reserved_books.item_sold',
             'reserved_books.date_sold',
             'reserved_books.total_amount'
);
$q = $this->db
        ->select($select)
        ->from('reserved_books')
        ->join('books','reserved_books.isbn= books.isbn')
        ->group_by('reserved_books.isbn')
        ->limit($limit,$offset);

回答5:

Here is best way to remove duplicate entry.

$query  =  $this->db->select("inf.id, inf.sku, inf.fab_id, inf.sku, inf.amount, inf.min_length, inf.num_of_pieces, inf.width ,inf.type") 
                            ->join("retailcat","retailcat.sku=SUBSTRING(inf.sku,1,19) AND retailcat.category=218","INNER") 
                            ->distinct()
                           ->get("input_factor inf");