CODEWITHSUNDEEP
sqlsql-server
Rajesh
Rajesh

Reputation: 1

how to identify the duplicate rows in a table

i have one table which 4 columns code1,code2,code3 and code4

so those can contain the data like below (showing 2 records below)

code1=xy code2=yz code3='' code4=''
code1=xy code2='' code3=yz code4=''

We need to make the 2 record as duplicate as it contains the same data scattered on differnt columns. please help me

Upvotes: 0

Views: 64

Answers (3)

Erfan Mohammadi
Erfan Mohammadi

Reputation: 444

You can use from This for find duplicate value

Detect duplicate items in recursive CTE

WITH cte AS (
SELECT ROW_NUMBER()OVER(PARTITION BY [FieldName] ORDER BY [FieldName])[Rank],* 
FROM TableName)
SELECT * 
FROM  cte 
WHERE cte.[Rank]>1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you don't care about the ordering of the columns, you can get the unique values by doing:

select distinct x.*
from t cross apply
     (select max(case when seqnum = 1 then col end) as col1,
             max(case when seqnum = 2 then col end) as col2,
             max(case when seqnum = 3 then col end) as col3,
             max(case when seqnum = 4 then col end) as col4
      from (select col, row_number() over (order by col desc) as seqnum
            from (values (col1), (col2), (col3), (col4)) v(col)
           ) x
     ) x

Upvotes: 0

Derek Tomes
Derek Tomes

Reputation: 4007

What you probably have here is a many to one relationship that someone has shoehorned into a single table and this will create more problems than it solves.

I'd suggest fixing your schema and then you'll be able to more easily solve this kind of problem.

For example if the columns code1, code2, code3 and code4 are on an employee table like this:

employee table
|employeeid |name |code1 |code2 |code3 |code4 |
|1          |bob  |xy    |yz    |      |      |
|2          |sam  |xy    |      |yz    |      |

Then you could restructure your data into two tables:

employee table
|employeeid |name |
|1          |bob  |
|2          |sam  |

code table
|employeeid |code |
|1          |xy   |
|1          |yz   |
|2          |xy   |
|2          |yz   |

This will make it much easier to find people with the same codes (and remove what is probably an arbitrary limit where you have four codes)

Upvotes: 0

Related Questions