shahalpk shahalpk - 6 months ago 8
SQL Question

Find duplicate entries in a table

I have a

messcuts
table with the following structure.

id
,
student_rollno
,
date
.

The problem is there are some records duplicated ie. two records with same
student_rollno
in the same
date
. How do I remove them? Eg:

SELECT *
FROM `messcuts`
WHERE student_rollno = 'b070226'

|id |student_rollno|date
|259|B070226|2011-08-06
|260|B070226|2011-08-07
|1485|B070226|2011-08-12
|1486|B070226|2011-08-13
|1487|B070226|2011-08-14
|1488|B070226|2011-08-15
|2372|B070226|2011-08-27
|2369|B070226|2011-08-24
|2368|B070226|2011-08-23
|2371|B070226|2011-08-26
|2374|B070226|2011-08-29
|2373|B070226|2011-08-28
|2370|B070226|2011-08-25
|2367|B070226|2011-08-22
|2375|B070226|2011-08-30
|2376|B070226|2011-08-31
|2938|b070226|2011-08-06


See on 2011-08-06 there are two records.

Answer
select student_rollno, date 
from messcuts 
group by student_rollno, date 
having count(*) > 1

and to delete:

delete from messcuts d where d.id in (
    select max(s.id) 
    from messcuts as s 
    group by s.student_rollno, s.date 
    having count(*) > 1)

if not working in mysql:

delete from messcuts 
using messcuts, messcuts as v_messcuts
where messcuts.id <> v_messcuts.id
and messcuts.student_rollno = v_messcuts.student_rollno
and messcuts.date = v_messcuts.date
Comments