Delete duplicate rows from dataset

Question

I have a dataset containing duplicate rows

DivID  Account_Code  Name
1         CDF400      Caledon
1         CDF400      Caledon
1         CDF400      Caledon
1         BRF205      Brightside
1         BRF205      Brightside
1         FRO400      Fireburn
1         FRO400      Fireburn

Could anyone supply me with a query to remove the duplicate lines

Thanks in advance

Answer 1

Use row_number() function to check duplication and remove

delete d from (
   select *,
          row_number() over (partition by Account_Code, Name order by DivID) Seq 
   from table
) d
where Seq  > 1

Answer 2

WITH CTE AS(

   SELECT DivID,
          Account_Code,
          Name,
        ROW_NUMBER() OVER (PARTITION BY DivID,Account_Code,Name ORDER BY (SELECT 0)) AS RN
   FROM Table1
)
DELETE FROM CTE WHERE RN > 1

Demo

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c17dad6539da425bc1371614f0cbb1b2

Answer 3

TRy this:

;WITH cte
     AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY DivID,Account_Code,Name 
                                       ORDER BY ( SELECT 0)) RN
         FROM   YourTable)
DELETE FROM cte
WHERE  RN > 1;

Answer 4

Try Using DistictFunction In Your Select Query .

     SELECT DISTINCT statement