Question 20334 in Mysql Question posted in
The official MySQL documentation can be found here.

Is there a way to print record without duplicate from a table? – Mysql

I have a table that have many records and others are duplicated. I want to print all the records out but I don’t want the duplicated to echo more than once.

I have a tried to add break when a record is detected to be duplicate but the iteration stopped their immediately.

This is the sql statement.

$query = $this->db->query(select * 
from mytable 
where status = '1' and cityname= 'abuja' and age= '27' 
order by id ASC);

foreach ($query->result_array() as $rowx){ 

echo $rowx['xxx'];

}
Tags:

3

Answers


  1. 0

    If I remember correctly from my SQL classes, you can use DISTINCT to get the list of your records without duplicate.

    A quick google query got me to this website: https://learnsql.com/cookbook/how-to-not-show-duplicates-in-sql/
    I think this is what you are looking for.

    Login or Signup to reply.
  2. 0

    Try to Use DISTINCT Keyword, It will resolve your query.

    Login or Signup to reply.
  3. 0

    If it is one time query – just use SELECT DISTINCT on all fields except unique(id), should work. For unique field you can use MIN(id) or similar.

    General answer for large tables is like this(for small tables just use distinct and delete all which not captured by select distinct):

    1. first you need at least one column NOT duplicated. if you have no such column, you have to add it(for example, id serial)

    2. after that you have join table with itself and compare all records like this

      select distinct b.id
      from table_name as a join table_name as b
      on a.id< b.id and a.field1=b.field1 and a.field2=b.field2
      /* all fields which matter except id */
      where b.id is not null

    Note, that you need an effective index on id+fieldx where is fieldx is quite unique, usually I use some date. Otherwise join will take too much time.

    So it will build pairs got you list of IDs which are duplicates. If you have much more then one duplicate per row it is wise to make reverse, i.e make list of unique IDs.

    WARNING: it is absolutely necessary check result before delete manually and not forget to add check a.id < b.id. Otherwise every row will be duplicate of itself. Also before issue that SQL use explain and ensure it IS using index for join.

    If your table is really large, you should consider:

    1. make SQL in transaction with non-blocking transaction level to not affect other operations
    2. make sharding using some field.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search