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.

Where an address is duplicated, which row do you wish to return in the resultset? the first by ID? — Ian Nelson
– Ian Nelson, Commented Sep 28, 2011 at 11:31
id 2 in not looking duplicate to any other rows.. why you want to remove this also.. — pratik garg
– pratik garg, Commented Sep 28, 2011 at 11:34

DavidEG · Accepted Answer · 2011-09-28 11:43:42Z

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, 2011 at 11:43

DavidEG

5,9673 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nimit Dudani Over a year ago

what if I need to keep lower id ?

DavidEG Over a year ago

Just change t1.id < t2.id by t1.id > t2.id

barryhunter · Accepted Answer · 2011-09-28 12:33:48Z

4

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

answered Sep 28, 2011 at 12:33

barryhunter

21.1k3 gold badges32 silver badges44 bronze badges

Comments

Ian Nelson · Accepted Answer · 2011-09-28 12:02:41Z

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, 2011 at 12:02

answered Sep 28, 2011 at 11:33

Ian Nelson

59.1k20 gold badges79 silver badges104 bronze badges

Comments

Nimit Dudani · Accepted Answer · 2011-09-28 13:39:30Z

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, 2011 at 13:39

answered Sep 28, 2011 at 12:17

Nimit Dudani

4,8603 gold badges33 silver badges46 bronze badges

3 Comments

DavidEG Over a year ago

Are you sure is not possible to do by one query? Check my answer, am I missing something?

Nimit Dudani Over a year ago

@DavidEG, yes it is possible. you genius. your solution is perfect one

Nimit Dudani Over a year ago

@maulik.patel, you can use above logic in more complex condition

pratik garg · Accepted Answer · 2011-09-28 11:41:12Z

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, 2011 at 11:41

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

Comments

user769394 · Accepted Answer · 2011-09-28 11:43:37Z

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

Collectives™ on Stack Overflow

Removing duplicate rows from a table

6 Answers 6

2 Comments

Comments

Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

3 Comments

Comments

Comments

Linked

Related