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.

Please read http://en.wikipedia.org/wiki/Database_normalization — YXD, Sep 28 '11 at 11:29
Where an address is duplicated, which row do you wish to return in the resultset? the first by ID? — Ian Nelson, Sep 28 '11 at 11:31
id 2 in not looking duplicate to any other rows.. why you want to remove this also.. — pratik garg, Sep 28 '11 at 11:34
Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — simhumileco, May 24 '19 at 10:13

score 9 · Answer 1 · answered Sep 28 '11 at 11:43

9

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

answered Sep 28 '11 at 11:43

DavidEG

5,857
3
29
44

what if I need to keep lower id ? – Nimit Dudani Sep 28 '11 at 13:40
Just change `t1.id < t2.id` by `t1.id > t2.id` – DavidEG Sep 28 '11 at 13:44

score 4 · Answer 2 · answered Sep 28 '11 at 12:33

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';

Ian Nelson · Accepted Answer · 2011-09-28T12:02:41.713

2

Assuming that you wish to return the rows with the smallest ID values:

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

edited Sep 28 '11 at 12:02

answered Sep 28 '11 at 11:33

Ian Nelson

57,123
20
76
103

Nimit Dudani · Answer 4 · 2011-09-28T13:39:30.863

2

Need to create duplicate/temporary table with same field what your current table have.

Then execute below SQL

First clear temporary table :

DELETE FROM `#TMP_TABLE#`;

Insert record in temporary table as per your expectation.

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

Truncate main table

DELETE FROM `#TABLE#`;

Copy Data from temporary table

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

edited Sep 28 '11 at 13:39

answered Sep 28 '11 at 12:17

Nimit Dudani

4,840
3
31
46

Are you sure is not possible to do by one query? Check my answer, am I missing something? – DavidEG Sep 28 '11 at 12:49
@DavidEG, yes it is possible. you genius. your solution is perfect one – Nimit Dudani Sep 28 '11 at 13:44
@maulik.patel, you can use above logic in more complex condition – Nimit Dudani Sep 28 '11 at 13:46

score 0 · Answer 5 · answered Sep 28 '11 at 11:41

0

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

I assumed that you want to remove rows having address duplicacy and want to keep minimum id's row in your table

answered Sep 28 '11 at 11:41

pratik garg

3,282
1
17
21

score 0 · Answer 6 · answered Sep 28 '11 at 11:43

You Can Do this Easily by Doing these 3 easy steps and therefore 3 SQL statements:

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

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

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

For Another Way You Can go to the Bellow Link... Its is Also a good Way... Difficult but with less statements http://dev.mysql.com/doc/refman/5.0/en/insert.html

Removing duplicate rows from a table

6 Answers6

Linked