Removing duplicate rows from a table

Question

id     lat                long     speed      date          address
 1    22.92138131   72.44103313     3.96 km/h     2011-09-26  National, Gujarat, India
 2    22.92138145   72.44103413     13.96 km/h     2011-09-26  National, Gujarat, India
 3    22.92138134   72.44103423     15.96 km/h     2011-09-26  National, Gujarat, India
 4    22.92138454   72.44103233     13.96 km/h     2011-09-26  10t ring Rd, Nehru Nagar
 5    22.92138354   72.44102533     13.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad
 6    22.92138484   72.44103293     19.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad

I want to write a query such that my result looks like this:

id     lat                long     speed      date          address
 1    22.92138131   72.44103313     3.96 km/h     2011-09-26  National, Gujarat, India
 4    22.92138454   72.44103233     13.96 km/h     2011-09-26  10t ring Rd, Nehru Nagar
 5    22.92138354   72.44102533     13.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad

I want to remove duplicate rows according to the address.

Answer 1

To check what you are going to delete:

SELECT distinct t1.*
  FROM yourtable as t1
  join yourtable as t2
 WHERE t1.address = t2.address
   and t1.id < t2.id

If you are happy with that:

DELETE t1
  FROM yourtable as t1
  join yourtable as t2
 WHERE t1.address = t2.address
   and t1.id < t2.id

This way you keep the record with the max value on id column

Answer 2

If you don't care which of the rows you keep

ALTER IGNORE TABLE table ADD UNIQUE KEY 'address' (`address`);

The 'IGNORE' is important, that means to silently ignore duplicate data. (ie ignores it when inserting into the 'new version' of the table.)

May want to remove the index afterwoods

ALTER TABLE table DROP KEY 'address';