Delete or ignore rows where several fields are duplicate

Question

My data is from electric suppliers. Some charges are fixed some are usage charge. I live a problem for fixed charges. When the electricity meter change during the billing period, two fixed charge lines are created with different meterID and contrat number. Other all fields are same and I want to get one of these. Because it is monthly fixed charge.

If you help me I will be happy, Thank you very much,

https://www.designcise.com/web/tutorial/how-to-remove-all-duplicate-rows-except-one-in-sql#:~:text=How%20to%20Remove%20All%20Duplicate%20Rows%20Except%20One,Duplicates%20and%20Keep%20Row%20With%20Highest%20ID%20 I created a view without these two fields and get unique fields and created another view and added that two fields, gave small values than real values for compare. My values for second view (A10000000' AS MeterUniqueNo, '100000' as MeterContractID) Original examples K18D01652, 646802)

delete from main_table 
Inner join view2 on view2.MeterUniqueNo < main_table.MeterUniqueNo
and view2.EnergyChargesRecord_InvoiceNumber = main_table.EnergyChargesRecord_InvoiceNumber
and view2.EnergyChargesRecord_MPANNumber = main_table.EnergyChargesRecord.MPANNumber

It is not working, because values are different.

T-SQL: Deleting all duplicate rows but keeping one

I can not use this method. Because I have to check MPAN number and Invoice number. Not just one value...

Note that tables have _columns_, not fields. – jarlh Dec 28 '22 at 19:45 — jarlh, Dec 28 '22 at 19:45

score 0 · Answer 1 · answered Dec 29 '22 at 21:18

Since you didn't provide any sample data, I'm guessing at the actual format of your data. I created a minimal example of how to use a ROW_NUMBER function to order duplicates and select only the most recent one. Again, I'm guessing at the sample data, but the common data between the duplicate rows is the MPAN_number column. This is only an example, provide sample data for better, or more-specific-to-your-application, answers.

--Create test table.
CREATE TABLE charges (
  meter_id int
  , contract_number int 
  , charge_amt decimal(19,2)
  , invoice_number int
  , MPAN_number nvarchar(100)
  , charge_date date
);

--Insert test data.
INSERT INTO charges (
  meter_id, contract_number, charge_amt, invoice_number, MPAN_number
  , charge_date)
VALUES 
 (123, 998, 25.54, 3216549, '123AM234ASF', '1/2/2022')
 , (456, 12399, 25.54, 3216668, '123AM234ASF', '1/15/2022')
 , (987, 887, 25.54, 3589765, 'K18D01652', '1/5/2022')
 , (654, 123488, 25.54, 3548892, 'K18D01652', '1/28/2022')
;

--For debugging, show all test data.
SELECT * FROM charges;

--Use a CTE to add a row_num column. 
--This row_num column will sequence "duplicate" charge lines by charge_date with the most recent charge as row_num = 1.
--The common data in the example data is the MPAN_number.
--This is only an example, for more specific help, you need to create
--a mimimal reproducible example just like this.
WITH prelim as (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY MPAN_number ORDER BY charge_date DESC) as row_num
  FROM charges
)
SELECT *
FROM prelim
WHERE row_num = 1
;

--Here's an example of how to delete all "duplicate" charges that are not the most recent charge.
DELETE c 
FROM charges as c
  INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY MPAN_number ORDER BY charge_date DESC) as row_num
    FROM charges
  ) as oldDups
    ON oldDups.MPAN_number = c.MPAN_number
    AND oldDups.meter_id = c.meter_id
    AND oldDups.contract_number = c.contract_number
    AND oldDups.row_num <> 1
;

--For debugging, show the test data after deletions.
SELECT * FROM charges;

Showing all test data:

meter_id	contract_number	charge_amt	invoice_number	MPAN_number	charge_date
123	998	25.54	3216549	123AM234ASF	2022-01-02
456	12399	25.54	3216668	123AM234ASF	2022-01-15
987	887	25.54	3589765	K18D01652	2022-01-05
654	123488	25.54	3548892	K18D01652	2022-01-28

Showing the most recent charges using SELECT:

meter_id	contract_number	charge_amt	invoice_number	MPAN_number	charge_date	row_num
456	12399	25.54	3216668	123AM234ASF	2022-01-15	1
654	123488	25.54	3548892	K18D01652	2022-01-28	1

Showing the test data remaining after a DELETE operation:

meter_id	contract_number	charge_amt	invoice_number	MPAN_number	charge_date
456	12399	25.54	3216668	123AM234ASF	2022-01-15
654	123488	25.54	3548892	K18D01652	2022-01-28

fiddle

Hi, I tried your method but unique all data and one of duplicate record are delete. I tried cursor. I need to order and for order I have to define delete/update/etc specify. Because I receive 'read only' error message. I have to define cursor too. I am defining but still it is 'read only' — Derya Sahin, Jan 12 '23 at 14:56
@DeryaSahin your comment doesn't help me solve your problem. Update your post with your current code so I can try to help you. Without your code, how can I help you? My answer is a minimally reproducible solution, I need you to provide the same. — Tim Jarosz, Jan 12 '23 at 15:00

Delete or ignore rows where several fields are duplicate

1 Answers1