Duplicate values

Question

I have a question regarding duplicate records,I need find duplicate records in 5columns  and 4 should be have same values and one should be different

Answer 1

Can you provide schema/DDL/sample data so we can better understand the problem?  Here are a few threads with solutions for finding duplicate values:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e033ff26-8bc9-4ff1-ba00-08219f4d0415

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/cf00619f-7875-4109-a76c-10acf6535b21

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Thanks Samuel,

Here is the example, see 3 and 4 are having same values in 4 records and 5 th one is different. I need to raise a flag either if the 5th one is different from  4 others. It should be any one field.

Last Name	First Name	Decision Date	DecisionNumber	SSN
Smith	John	20120106	0	XXX-XX-1234
Smith	John	20120106	0	XXX-XX-1234
Smith	John	20120106	1	XXX-XX-1234
Smith	John	20120106	1	XXX-XX-4567

Answer 3

@Samuel : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/cf00619f-7875-4109-a76c-10acf6535b21

Answer 4

@Samuel Lester : bro, help me !!! i have stopped working on  it due to this confusion

chk my post above !!!

Answer 5

I need a fuzzy logic, we have 5 fields in a table and 4 fields values are same and one is different. It should be any field in that 5 fields.

Let me know if still it confuses. you.

Answer 6

Here is the best example.

Suppose I have a SQ L table like this:
+-----+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |  E  |
+=====+=====+=====+=====+=====+
|  1  |  2  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  2  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  2  |  4  | foo |  bar|
+-----+-----+-----+-----+-----+
|  1  |  4  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  2  |  5  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | tom | bar |  
+-----+-----+-----+-----+-----+
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
 

Now, I want to the below result
+-----+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |  E  |
+-----+-----+-----+-----+-----+
|  1  |  2  |  4  | foo |  bar|
+-----+-----+-----+-----+-----+
|  1  |  4  |  5  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | tom | bar |  
+-----+-----+-----+-----+-----+

 

Answer 7

Hello Naveen,

you can use join concept on single table and use a key column to match duplicate values.

Answer 8

http://dimantdatabasesolutions.blogspot.co.il/2007/02/dealing-with-duplicates.html

Add join condition to the correlated subquery to get final result.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 9

Thank you all, Finally I got the query.

select A.last_name, A.first_name, A.Dec_num, A.Dec_Date, A.Address

from State_Dis A

Cross join State_Dis B

where A.state = B.state

and (

(A.last_name = B.last_name and A.first_name = B.first_name and A.Dec_num = B.Dec_num and A.Dec_Date = B.Dec_Date and A.Address <> b.ADDRESS)

or

(A.last_name = B.last_name and A.first_name = B.first_name and A.Dec_num = B.Dec_num and A.Dec_Date <> B.Dec_Date and A.Address = b.ADDRESS)

or

(A.last_name = B.last_name and A.first_name = B.first_name and A.Dec_num <> B.Dec_num and A.Dec_Date = B.Dec_Date and A.Address = b.ADDRESS)

or

(A.last_name = B.last_name and A.first_name <> B.first_name and A.Dec_num = B.Dec_num and A.Dec_Date = B.Dec_Date and A.Address = b.ADDRESS)

or

(A.last_name <> B.last_name and A.first_name = B.first_name and A.Dec_num = B.Dec_num and A.Dec_Date = B.Dec_Date and A.Address = b.ADDRESS))

order by a.Address,a.last_name,a.first_name