0

I have a transaction table (SQL Express 2014) that hold sales transactions. I need to remove duplicate records, retaining only the most recent record

Example of current data

ACC_PART_MAT      TX_DATE     
A1025-A552        2021-09-02
A1025-B1994       2121-04-28
A1025-B1994       2121-09-02
A1025-B1994       2121-03-21
A1025-B1960       2121-05-20

End result required

ACC_PART_MAT      TX_DATE     
A1025-A552        2021-09-02
A1025-B1994       2121-09-02
A1025-B1960       2121-05-20

There are many examples addressing duplicate records but I cannot get them work with no primary key and dates. Many thanks in advance

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
WillSmyth
  • 3
  • 3
  • So are you going to perform this delete after every time you sell a new part? Or are you going to fix the design or how you store sales transactions (maybe history in a different table, or update the existing row for a given part, or just have a view that exposes only the most recent date per part)? – Aaron Bertrand Sep 23 '21 at 10:57

1 Answers1

1

For your example, you can just use aggregation:

select ACC_PART_MAT, min(TX_DATE) as TX_DATE
from t
group by ACC_PART_MAT;

If you actually wanted to delete rows from a table, you can use an updatable CTE -- but be careful because this changes the table:

with todelete as (
      select t.*,
             row_number() over (partition by ACC_PART_MAT order by TX_DATE asc) as seqnum
      from t
     ) 
delete from todelete
    where seqnum > 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Selecting rather than deleting will suffice for my requirements. The select code provided is perfect but how would this be impacted if the table has additional fields eg if table layout is ACC_PART_MAT, TX_DATE, CUST_NO, CUST_NAME, PRICE – WillSmyth Sep 23 '21 at 11:22
  • Deleting via CTE works! (with TX_DATE DESC). Brilliant. Thanks – WillSmyth Sep 23 '21 at 11:33
  • @WillSmyth . . . Just as a note: updatable CTEs are a feature specific to SQL Server. I don't think they work in any other database. – Gordon Linoff Sep 23 '21 at 12:05