How to get rid of duplicate rows in MySQL

Question

Possible Duplicate:
MySQL Duplicate rows
how to delete duplicate rows from a table in mysql

I have data that looks something along the lines of:

22205 | 2179 |  85 
 8306 | 2179 | 178
11164 | 2179 | 178
23873 | 2179 | 178
 8308 | 2179 | 314
22203 | 2179 | 314
22201 | 2178 |  85

I'm trying to figure out a query that will let me remove the duplicates of the third column. As in we should only have one 178 and one 314. But keeping in mind that it can duplicate as long as the second column doesn't duplicate so final result should look like:

22205 | 2179 |  85 
 8306 | 2179 | 178
22203 | 2179 | 314
22201 | 2178 |  85

Can anyone help?

Difference being my third column isn't the key AND the second column can have duplicates. — Peter, Jun 25 '12 at 20:49
@Dil, I'm just guessing, but the first column is typically the `PRIMARY` key and won't have duplicates. — 0b10011, Jun 25 '12 at 20:57

score 2 · Accepted Answer · answered Jun 25 '12 at 20:53

Simply use GROUP BY (example):

SELECT *
FROM `Table1`
GROUP BY `b`, `c`

Assuming:

CREATE TABLE Table1
    (`a` int, `b` int, `c` int);

INSERT INTO Table1
    (`a`, `b`, `c`)
VALUES
    (22205, 2179, 85),
    (8306, 2179, 178),
    (11164, 2179, 178),
    (23873, 2179, 178),
    (8308, 2179, 314),
    (22203, 2179, 314),
    (22201, 2178, 85);

score 0 · Answer 2 · answered Jun 25 '12 at 20:51

0

Maybe you need to create a composite key on both columns. Create a new temp table lets say 'tbl_temp' then copy over all rows using INSERT iGNORE ...

answered Jun 25 '12 at 20:51

Sabeen Malik

10,816
4
33
50

How to get rid of duplicate rows in MySQL

2 Answers2