sql query to find duplicate values

sql query to find duplicate values


"SELECT name, email COUNT(name) AS NumOccurName, COUNT(email) as NumOccurEmail FROM users GROUP BY Name, Email HAVING ( COUNT(name) > 1 ) AND ( COUNT(email) > 1)"


select * from table1 a where rowid>(select min(rowid) from table2 b where a.col1=b.col1 and a.col2=b.col2…)


SELECT * FROM maintable p WHERE ((SELECT COUNT(id) FROM maintable WHERE p.columnwithdupes = maintable.columnwithdupes AND p.id <> maintable.id) > 0)


Delete dup


delete from test
where exists(
select * from to_delete
where to_delete.day = test.day and to_delete.min_id <> test.id
)

difference between where clause and having clause
Why can’t you use a WHERE clause?
A WHERE clause filters the rows before they are grouped together. A HAVING clause filters them after grouping. That’s why you can’t use a WHERE clause in the above query.