Delete duplicate record in sql server

7xzttuei  于 2023-04-28  发布在  SQL Server
关注(0)|答案(4)|浏览(159)
ColAColBColC
A112
A212
A312
B112
B212
B312
C112
C212
C312

I have such table data. And expect result is:

ColAColBColC
A112
B212
C312

or

ColAColBColC
A312
B212
C112

or

ColAColBColC
A112
B312
C212

If there are 4 record inserted, then it will join and become 16(4*4) records in final temp table. How do I delete unnecessary data in this case?
This is what tried

WITH cte AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS Row_count 
  FROM table_1
)
DELETE FROM cte WHERE rn<>1;

And I got:

ColAColBColC
A112
B112
C112

which is wrong.

ColB data should be not be duplicated. After deleting, every record must be unique.

vfwfrxfs

vfwfrxfs1#

You can use combination of two partitions and check for differences:

WITH cte AS (
  SELECT ColA, ColB, ColC,
         ROW_NUMBER() OVER (PARTITION BY ColA ORDER BY ColB) AS Row_count1, 
         ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA) AS Row_count2 
  FROM table_1
)
DELETE FROM   cte WHERE Row_count1<>Row_count2

See the db fiddle here https://dbfiddle.uk/aLsQLHIt

goqiplq2

goqiplq22#

For this sample data all you need is DENSE_RANK() window function to get a group number for each value of ColA :

WITH cte AS (SELECT *, DENSE_RANK() OVER (ORDER BY ColA) rn FROM t)
DELETE FROM cte WHERE ColB <> rn;

See the demo .

pkbketx9

pkbketx93#

I'd to rank lines twice: first time on colB, colC and on colA so, the intermediate results would be:
| ColA | ColB | ColC | rank_bc | rank_a |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| A | 1 | 12 | 1 | 1 |
| A | 2 | 12 | 2 | 1 |
| A | 3 | 12 | 3 | 1 |
| B | 3 | 12 | 3 | 2 |
| B | 2 | 12 | 2 | 2 |
| B | 1 | 12 | 1 | 2 |
| C | 1 | 12 | 1 | 3 |
| C | 2 | 12 | 2 | 3 |
| C | 3 | 12 | 3 | 3 |

After that all is left to do is just to filter lines with rank_bc = rank_a

with test_data_ranked as (  
select ColA, ColB, ColC, 
       dense_rank() over (order by colB, colC) rank_bc,
       dense_rank() over (order by colA) rank_a
  from test_data)

select colA, colB, colC
  from test_data_ranked
 where rank_bc = rank_a;

dbfiddle

uinbv5nw

uinbv5nw4#

You Can solve your problem with cte and window function with use query store

;WITH cte AS (

select *,row_number() OVER (PARTITION BY Rw  order by ColB ) AS Rwn from (
  SELECT *, 
         row_number() OVER (PARTITION BY ColB  order by colA ) AS Rw
  FROM Ta
  )d

)
delete  FROM cte WHERE Rwn>1;

Result:

ColAColBColC
A112
B112
C112

相关问题