How to find only duplicate records from the table in SQL server?
Nilesh Avhad
select name from emp group by name HAVING COUNT(*) > 1
Please refer the article Remove duplicate records/data from Sql Server database table http://www.webcodeexpert.com/2013/11/how-to-remove-duplicate-recordsdata.html
select id, count(*) as numberduplicate from TableName group by id having count(*) >1 order by id
select COLUMNNAME from TABLENAME group by COLUMNNAME having COUNT(COLUMNNAME )>1
I agree with Sandesh's answer
with x as (select *, rn=ROW_NUMBER()over(PARTITION BY username, userrole order by userid) from t_user )select * from x where rn>1