I am using SQL Server . i have creating SQL table have more than 20000 lines. i have Filter duplicate Using Following Query.
SELECT
Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate,COUNT(*)
FROM
TotalsByGLCenter
GROUP BY
Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate
HAVING
COUNT(*) > 1
now I want to delete Duplicate from SQL Server How to add delete Following above Query ?
;WITH cte
AS (Select ROW_NUMBER() OVER (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum
FROM TotalsByGLCenter)
Delete FROM cte
WHERE RowNum > 1;
The “ROW_NUMBER()” in the above query returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. The “ORDER BY” clause determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. The “PARTITION BY” clause used here to divide the result set into partitions;
The new column RowNum shows row numbers of the duplicate rows.
In case if you want to see the duplicate rows you can use select instead of delete
;WITH cte
AS (Select ROW_NUMBER() OVER (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum,Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate
FROM TotalsByGLCenter)
Select * FROM cte
WHERE RowNum > 1;
Here's a (simplified) example
drop table t
create table t (id int)
insert into t values (1),(1),(1),(2),(3),(3)
;WITH CTE AS(
select id,count(*) as dupes
from t
group by t.id
having count(*) > 1
)
DELETE t where t.id in (select cte.id from cte)
result
1> select * from t
2> ;
3> go
id
-----------
2
you could also create a new regular table with the same structure, insert distinct data in that table, then delete your original table and rename new table to the same name as original table had
Primary Keyin your table?