Duplicate values

Duplicate values

• 17. srpna 2012 15:24

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

Všechny reakce

• 17. srpna 2012 15:37

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:

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.

• 17. srpna 2012 15:51

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
• 17. srpna 2012 16:52

• 17. srpna 2012 16:54

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

chk my post above !!!

• 17. srpna 2012 17:04

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.

• 17. srpna 2012 17:31

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 |
+-----+-----+-----+-----+-----+

• 19. srpna 2012 4:52

Hello Naveen,

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

• 19. srpna 2012 9:23
Přispěvatel

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/

• 22. srpna 2012 19:53

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))