MySQL remove duplicates

Question

There is a table of the form:

NAME |    DATE    | INFO  | SPEC
Bob  | 01.01.2010 | Text1 | Student
Ann  | 02.02.2020 | Text2 | Secretary
Bob  | 01.01.2010 | Text1 | Student
John | 03.03.2030 | Text3 | Tester
Bob  | 01.01.2010 | Text1 | Student

How can I get a table (in request) like this:

NAME |    DATE    | INFO  | SPEC
Bob  | 01.01.2010 | Text1 | Student
Ann  | 02.02.2020 | Text2 | Secretary
John | 03.03.2030 | Text3 | Tester

PS: These variants aren't working:

SELECT DISTINCT Name, Date, Info, Spec FROM Table
SELECT DISTINCT * FROM Table
SELECT Name, Date, Info, Spec FROM Table GROUP BY Name, Date, Info, Spec
SELECT * FROM Table GROUP BY Name, Date, Info, Spec

Consider using this answer http://stackoverflow.com/questions/3383898/remove-duplicates-using-only-a-mysql-query — Matthew Thomas, Apr 07 '14 at 08:28
@All answers, Title says, user wants to delete duplicate rows, not only select. — AK47, Apr 07 '14 at 08:43
@user3505828 do your table has more columns?, please share table schema. — AK47, Apr 07 '14 at 08:45

score 0 · Answer 1 · answered Apr 07 '14 at 08:27

0

You need to use the DISTINCT keyword

Try a query like this:

SELECT DISTINCT * FROM table

answered Apr 07 '14 at 08:27

marcosh

8,780
5
44
74

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

Use

SELECT DISTINCT * FROM MyTable

However, you would be better off getting rid of duplicates in your table. See Remove duplicate rows in MySQL to find out how.

edited May 23 '17 at 12:05

Community

1
1

answered Apr 07 '14 at 08:27

Tarik

10,810
2
26
40

score 0 · Answer 3 · answered Apr 07 '14 at 08:28

Use DISTINCT. From the docs:

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set.

So use something like this:

SELECT DISTINCT * from tablename;

score 0 · Answer 4 · answered Apr 07 '14 at 08:35

If there is Primary key other than mentioned columns, then you can use this code,

delete from myTable where pkID not in
(
select min(pkID) from myTable
group by NAME,DATE ,INFO ,SPEC
)

Assuming that pkID is primary key

If there is no other column than these 4 columns, then do it in following way, Using Temp Tables.

Create table #tmp
(
    NAME varchar(50),
    DATE date,
    INFO varchar(50),
    SPEC varchar(50)
)
insert into #tmp
select distinct * from myTable


delete from myTable

insert into myTable
    select * from #tmp

Code is of SQL Server, hope it helps you.

score 0 · Answer 5 · answered Apr 07 '14 at 08:39

0

Just use DISTINCT in your select query

answered Apr 07 '14 at 08:39

Systematix Infotech

2,345
1
14
31

MySQL remove duplicates

5 Answers5