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 so 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 lines according to address.

Answer 1

Assuming you want to return the rows with the smallest identifier values:

 SELECT * FROM TABLENAME T INNER JOIN ( SELECT MIN(ID) AS ID FROM TableName GROUP BY Address ) SUB ON T.ID = SUB.ID 

Answer 2

To verify that you are about 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 satisfied with this:

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

This way you save the record with the maximum value in the id column

Answer 3

If you do not care which line you save

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

"IGNORE" is important, which means silently ignoring duplicate data. (ie, ignores it when pasted into the "new version" of the table.)

You may need to remove the afterwoods index

 ALTER TABLE table DROP KEY 'address'; 

Answer 4

You must create a duplicate / temporary table with the same field as your current table.

Then execute below SQL

First cleared temporary table:

 DELETE FROM `#TMP_TABLE#`; 

Paste the entry into the temporary table as you expect.

 INSERT INTO `#TMP_TABLE#` SELECT T . * FROM #TABLE# T INNER JOIN ( SELECT MIN( ID ) AS ID FROM #TABLE# GROUP BY address ) SUB ON T.id = SUB.id 

Trim the main table

 DELETE FROM `#TABLE#`; 

Copy data from temporary table

 INSERT INTO #TABLE# SELECT * FROM `#TMP_TABLE#` 

Answer 5

 delete from table_name tb where id not in (select min(id) from table_name tb1 group by address) 

I suggested that you want to delete rows with duplicate addresses and want to keep the minimum row id in the table

Answer 6

You can do this easily by following these 3 simple steps and therefore 3 SQL statements:

Step 1: Move non-duplicates (unique tuples) to the temporary table CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates];

Step 2: delete the old table We no longer need a table with all duplicate entries, so release it! DROP TABLE old_table;

Step 3: rename new_table to the name old_table RENAME TABLE new_table TO old_table;

For another way, you can go to Bellow Link ... This is also a good way ... Difficult, but with less statements http://dev.mysql.com/doc/refman/5.0/en/insert.html