SQL query duplicate record, delete duplicate record method Daquan

1. Querying duplicate Record single fields

SELECT * from TBL a where exists (select 1 to tbl B where A.username=b.username GROUP by username have count (*) > 1)

2. Number of query repetitions

Select Clistname,count (clistname) from Clalist GROUP by Clistname have Count (*) >1

3. Delete duplicate records, leaving ROWID minimum records

Delete from people
where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1)
and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)

4. Querying duplicate records, multiple fields

SELECT *
From Clalist a where exists
(

Select 1 from clalist b
where A.clalistid=b.clalistid and A.schsno=b.schsno
GROUP BY Clalistid,schsno have count (*) > 1

)
ORDER BY Schsno,clalistid

5. Delete duplicate records, multiple fields

Delete from Clalist where exists
(select 1 from clalist b
where Clalist. Clalistid=b.clalistid and Clalist. Schsno=b.schsno
GROUP BY Clalistid,schsno have count (*) > 1)
and Clalistsno not in (select min (clalistsno) from Clalist GROUP by CLALISTID,SCHSNO have count (*) > 1)

SQL query duplicate record, delete duplicate record method Daquan