Home » SQL & PL/SQL » SQL & PL/SQL » How to find a Nearly Duplicate Record in table
How to find a Nearly Duplicate Record in table [message #205645] Sun, 26 November 2006 23:30 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
I have got a
tab1 with 5 fields

tab1(field1,field2,field3,field4)

Data in tab1
tab1(aa,11,32,34)
tab1(aa,11,32,35j)
tab1(bb,15,2,1)
tab1(aa,11,,)
tab1(bb,15,2,2p)

Que: how i can achive below result

tab1(aa,11,32,34)
tab1(aa,11,32,35j)----near to duplicate
tab1(bb,15,2,1)
tab1(bb,15,2,2p)---- near to duplicate

 I want a record(s) which are exactly similar except one of the field is different so its not duplicate but nearly duplicate.



thanks

[Updated on: Sun, 26 November 2006 23:40]

Report message to a moderator

Re: How to find a Nearly Duplicate Record in table [message #205739 is a reply to message #205645] Mon, 27 November 2006 05:07 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Very quick and dirty:
WITH yourtable AS ( SELECT 'aa' c1,11 c2,32 c3,'34' c4   FROM dual 
                    UNION ALL
                    SELECT 'aa' c1,11 c2,32 c3,'35j' c4  FROM dual
                    UNION ALL
                    SELECT 'bb' c1,15 c2,2 c3,'1' c4     FROM dual
                    UNION ALL
                    SELECT 'aa' c1,11 c2,null c3,null c4 FROM dual
                    UNION ALL
                    SELECT 'bb' c1,15 c2,2 c3,'2p' c4    FROM dual
                  )
SELECT * FROM yourtable
WHERE ( c1,c2,c3) IN ( SELECT c1, c2, c3
                       FROM   yourtable
                       GROUP BY c1, c2, c3 HAVING COUNT(*) > 1
                     )
/


MHE
Previous Topic: the first of each group
Next Topic: what is the difference between SQL ,PL/SQL , ORACLE ? i want with examples
Goto Forum:
  


Current Time: Thu Dec 08 04:22:57 CST 2016

Total time taken to generate the page: 0.08563 seconds