SQL Scripts to Remove Duplicate records

Question

Need SQL Scripts to Remove Duplicate records. Below is the sample data • Example 1 : Before Running the script

CreatedWhen               Status    CVGUID      LocGUID 
-----------------------  ---------------- --------------- -------------
2015-11-04 11:00:00.667    CUR    65400270        5300610      
2015-11-04 10:44:36.327    PRV    65400270        5300610      
2015-11-04 10:43:03.340    INA    65400270        5300610      
2015-11-04 10:39:34.100    INA    65400270        5300610      
2015-11-04 10:35:17.880    INA    65400270        5300610

• Example 1 : After Running the script

CreatedWhen              Status ClientVisitGUID LocationGUID  
----------------------- ---------------- --------------- -------------
2015-11-04 11:00:00.667   CUR    65400270        5300610

• Example 2 : Before Running the script

CreatedWhen              Status CVGUID          LocGUID 
 -----------------------  ------ ---------------- -------------
 2015-11-09 04:41:16.340  CUR    65500270         6000610      
 2015-11-09 04:40:00.527  PRV    65500270         6000610      
 2015-11-09 04:38:13.790  INA    65500270         5300610      
 2015-11-09 04:33:23.843  INA    65500270         5300610

• Example 2 : After Running the script

CreatedWhen              Status CVGUID          LocGUID 
 -----------------------  ------ ---------------- -------------
2015-11-09 04:41:16.340  CUR    65500270         6000610            
 2015-11-09 04:33:23.843 PRV    65500270         5300610

• Example 3 : Before Running the script

CreatedWhen              Status CVGUID          LocGUID 
 -----------------------  ------ ---------------- -------------
2015-11-09 06:07:19.880  CUR    65600270         6000610      
 2015-11-09 06:06:48.970  PRV    65600270         5300610      
 2015-11-09 06:06:24.890  INA    65600270         7100610      
 2015-11-09 06:06:00.330  INA    65600270         6000610      
 2015-11-09 06:03:52.070  INA    65600270         5300610

• Example 3 : After Running the script(There should not be any change)

CreatedWhen              Status CVGUID          LocGUID  
 -----------------------  ------ ---------------- -------------
 2015-11-09 06:07:19.880  CUR    65600270         6000610      
 2015-11-09 06:06:48.970  PRV    65600270         5300610      
 2015-11-09 06:06:24.890  INA    65600270         7100610      
 2015-11-09 06:06:00.330  INA    65600270         6000610      
 2015-11-09 06:03:52.070  INA    65600270         5300610

Possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Ivan Starostin, Jul 21 '16 at 11:25

score 0 · Answer 1 · answered Jul 21 '16 at 11:17

Split your data into columns using space as a delimiter
Use ROW_NUMBER() OVER (PARTITION BY CVGUID, LOCGUID ORDER BY CreatedWhen) to mark duplicates
Delete all of the rows with row_number > 1

If you do not know how to carry out the steps above I would recommend to find answers using google or stack overflow, as these are pretty common problems

score 0 · Answer 2 · answered Jul 21 '16 at 11:19

Try this:

With cte as
(
  SELECT MAX(CreatedWhen) CreatedWhen, CVGUID, LocGUID
  INTO #temp
  FROM TableName
  GROUP BY CVGUID, LocGUID
)
With cte2 as
(
  SELECT CreatedWhen, ROW_NUMBER() OVER (ORDER BY CreatedWhen DESC PARTITION BY CVGUID) row_num, CVGUID, LocGUID
  FROM cte 
)
SELECT CreatedWhen, CASE WHEN row_num = 1 THEN 'CUR' WHEN row_num = 2 THEN 'PRV' ELSE 'INA' END Status, CVGUID, LocGUID
FROM cte2;

Let me know if there is any problem with the solution.

SQL Scripts to Remove Duplicate records

2 Answers2