Complex LINQ & SQL Queris

Complex LINQ & SQL Queris

LINQ:

linq query to remove duplicates

var distinctItems = items.GroupBy(x => x.Id).Select(y => y.First());

SQL:

sql query to delete duplicate records

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1
Method 3:

Using "Delete Top( )" clause:

If you want to delete duplicate rows for a particular empid then use "Top()" command in delete query as shown below.
    delete top(2) From EmpDup where empid=2
OR
    delete top(select count(*)-1 From EmpDup x where x.empid=2) From EmpDup where empid=2
    

Method 4:

If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.
Query to delete 3 duplicated rows (in our example table) or repeated more than 1 time.
    delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)