CODEWITHSUNDEEP
mysql
MRA
MRA

Reputation: 277

How do I delete duplicate values with group by

Example database :

ID StudentName StudentClass
1  John        A
2  John        B
3  Peter       A
4  John        A
5  John        B

I want the result should be

ID StudentName StudentClass
1  John        A
2  John        B
3  Peter       A

Statment 

DELETE FROM Student
 WHERE ID NOT IN (SELECT * 
                    FROM (SELECT MIN(n.ID)
                            FROM Student n
                        GROUP BY n.StudentName) x)

How do I keep John name on class A & B?

Upvotes: 2

Views: 117

Answers (4)

Kanwal Sarwara
Kanwal Sarwara

Reputation: 413

This should work:

DELETE S FROM Student S
INNER JOIN(
    SELECT MIN(ID) AS ID,StudentName,StudentClass FROM Student
    GROUP BY StudentName,StudentClass
) S2 ON S.ID != S2.ID AND S.StudentName = S2.StudentName AND S.StudentClass = S2.StudentClass

Its basically selecting the minimum ID out of all the duplicate records in sub query. Then we simply delete everything that matches that Class and Name, But we don't match the Minimum Id, so at end of day, we are keeping (presumably) 1st record out of duplicates and eradicating rest.

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

A better method to disallow even insertion of such duplicates would be multi-column unique index(it will optimize your searches too). Here is how:

ALTER TABLE `Student`
  ADD UNIQUE INDEX `idx` (`StudentName`, `StudentClass`)

Upvotes: 2

Zane Bien
Zane Bien

Reputation: 23125

DELETE a FROM Student a
LEFT JOIN
    (
        SELECT MIN(ID) AS minid
        FROM Student
        GROUP BY StudentName, StudentClass
    ) b ON a.id = b.minid
WHERE
    b.minid IS NULL

Upvotes: 2

Chris Trahey
Chris Trahey

Reputation: 18290

You should be able to join Students against itself, with a JOIN predicate that ensures the JOIN matches duplicate students, and delete the join'd row:

DELETE 
  duplicate_students.* 
FROM Students JOIN Students as duplicate_students 
  ON Students.StudentName = duplicate_students.StudentName
  AND Students.StudentClass = duplicate_students.StudentClass
  AND duplicate_students.ID > Students.ID

NOTE: Please back up your data first; I take no responsibility for lost data :-) This is a conceptual idea and has not been tested.

Upvotes: 0

Related Questions