0

Ok, so a flaw in my code created a bunch of duplicate statements for the same charge. They have the same ResidentID and same PostedOn date, but the different IDs and StatementIDs

ID  |  ResidentID  |  StatementID  |  PostedON
1         4039            10          06-15-18
2         4039            11          06-15-18
3         4039            12          06-15-18

4         4039            20          06-20-18
5         4039            21          06-20-18
6         4039            22          06-20-18

7         3456            13          06-15-18
8         3456            14          06-15-18
9         3456            15          06-15-18

10        3456            23          06-21-18
11        3456            24          06-21-18

I have a long list of many ResidentIDs that have duplicates in many dates. How do I delete all rows except the row with the lowest StatementID per unique date for that residentID

Example: resident 3456 would return statement 13 and 23.

This is in sql server, tsql.

How does the Delete Statement work for this. I have:

DELETE FROM Statements 
FROM (SELECT *, 
             ROW_NUMBER() 
             OVER (
                  PARTITION BY ResidentID, PostedON, PayerTypeID    
                  ORDER BY StatementNumber
                  ) seq
    FROM Statements
    ) s
        INNER JOIN person p ON s.ResidentID = p.ID
WHERE seq > 1
AND p.FacilityID = 7
AND p.id = 1316

but it seems to delete everything from that table. Am I wrong?

Mr9mm
  • 19
  • 3

1 Answers1

0

Use row_number() function :

delete t 
from (select *, 
             row_number() over (partition by ResidentID, PostedON order by StatementID) seq
      from table 
     ) t
where seq > 1;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52