OraFAQ Forum: SQL & PL/SQL » duplicate check by names

Home » SQL & PL/SQL » SQL & PL/SQL » duplicate check by names

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

duplicate check by names [message #9600]

Sun, 23 November 2003 00:06

kritika
Messages: 5
Registered: November 2003

Junior Member

if we are tring to check duplicate names in a table can we write

slect a.lname
from name a, name b
where a.lname = a.lname ( OR SHOULD IT BE A 'LIKE' OPERATOR) where a.lname like b.lname ?

or does it not matter if we use any of '=' or 'like'

Cheers
Kritika

Report message to a moderator

Re: duplicate check by names [message #9602 is a reply to message #9600]

Sun, 23 November 2003 02:28

Adam
Messages: 15
Registered: October 2001

Junior Member

As far as I know theres a big difference between = and like.

From what I gather, = means replace this value with this value. e.g. Name = Surname

What that is doing is replacing the Surname Value, with the Name value.

The like operator checks the reference of the information, e.g. is this information the same as this information....much like the .equals operator in Java.

I may be completly wrong and someone is proably gonna correct me. But if they don't...I can only assume mes is right...which will make a change

Report message to a moderator

Re: duplicate check by names [message #9603 is a reply to message #9600]

Sun, 23 November 2003 02:29

Adam
Messages: 15
Registered: October 2001

Junior Member

As far as I know theres a big difference between = and like.

From what I gather, = means replace this value with this value. e.g. Name = Surname

What that is doing is replacing the Name Value, with the SurName value.

The like operator checks the reference of the information, e.g. is this information the same as this information....much like the .equals operator in Java.

I may be completly wrong and someone is proably gonna correct me. But if they don't...I can only assume mes is right...which will make a change

Report message to a moderator

Re: duplicate check by names [message #9610 is a reply to message #9600]

Sun, 23 November 2003 23:49

Maaher
Messages: 7065
Registered: December 2001

Senior Member

The query you have written, won't work. It will just select every record at least once (duplicate names will appear 4 times, etc...). You COULD include something like:

Select <B>DISTINCT</B> ROWID
     , a.lname
  From name a
     , name b
 Where a.name = b.name
<B>   And a.ROWID != b.ROWID</B>;

ROWID is the internal id of a record. It cannot be modified but it can be useful because it's guaranteed to be unique. You can tell Oracle to look for identical names with a different rowid, hence in a different record. The distinct keyword makes sure that the result set contains no duplicates. Only distinctive results will be displayed.

But I'd rather do something like this:

Select a.lname
     , count(*) cnt
  From name a
 <B>Group By a.lname
Having COUNT(*) > 1;</B>

cnt is the number of times a name appears. Only names that appear more than once will be selected.

From the docs:
----------------------------------------------------------------------
The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set. LIKEC uses Unicode complete characters. LIKE2 uses UCS2 codepoints. LIKE4 uses USC4 codepoints.
----------------------------------------------------------------------

MHE

Report message to a moderator

Re: duplicate check by names [message #9642 is a reply to message #9610]

Tue, 25 November 2003 17:52

kritika
Messages: 5
Registered: November 2003

Junior Member

Thanks for the above. But these duplicates actually have different recordIds but same record data. This is giving trouble in that I can't avoid duplicates being selected. I know that its possible to avoid them through PL/SQL scripts but I just like to know If its possible with SQL Scripts ?

Thanks
Kritika

Report message to a moderator

Re: duplicate check by names [message #9647 is a reply to message #9642]

Tue, 25 November 2003 23:50

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Can you explain your problem a little further:
- you are looking for duplicates (seer your original post) and you can't avoid duplicates being selected? What do you mean? Best is that you provide some table layout, your select statement, a fraction of the result you are getting ( you can replace the actual data by dummy data ) and indicate what "goes wrong".

MHE

Report message to a moderator

Re: duplicate check by names [message #9655 is a reply to message #9647]

Wed, 26 November 2003 20:36

kritika
Messages: 5
Registered: November 2003

Junior Member

Sorry, about the confusion. I am trying to count and display duplicate records with a match based on person name. My SQL is

select distinct a.id, a.fname, a.lname
from name a, name b, duplicate
where a.lname = b.lname and
a.fname = b.fname and
a.id = duplicate.id

My table data looks something like this with 3 columns- Id, FirstName, LastName

And my current result looks like this

45 John Smith
89 George Harrop
65 John Smith
70 George Harrop
I would like a result like this below( without duplicates records being displayed and also so that I can count duplicates aswell) -

45 John Smith
89 George Harrop

And also this dupliacte condition is maintained in a table duplicate with these columns

id dup_id dup_flag
And table data looks like this
45 65 Y

Any ideas here ? Again I am looking only for SQL Script not PL/SQL

Thank You
Kritika

Report message to a moderator

Re: duplicate check by names [message #9665 is a reply to message #9655]

Thu, 27 November 2003 23:48

Maaher
Messages: 7065
Registered: December 2001

Senior Member

If I'm not mistaken, you can identify the original records by their id in the duplicate.id column. Than you could do sth like:

SQL> Select * from t;

        ID FNAME                     LNAME
---------- ------------------------- -------------------------
        45 John                      Smith
        89 George                    Harrop
        65 John                      Smith
        70 George                    Harrop

SQL> Select * From dups;

        ID     DUP_ID DUP_FLAG
---------- ---------- --------
        89         70 Y
        45         65 Y

SQL> Select t.id
  2       , t.fname
  3       , t.lname
  4    From t
  5   Where t.id In ( Select id
  6                     From dups
  7                 )
  8  /

        ID FNAME                     LNAME
---------- ------------------------- -------------------------
        45 John                      Smith
        89 George                    Harrop

SQL>

MHE

Report message to a moderator

Previous Topic:	How to use V$Session in Before Delete Trigger
Next Topic:	thanks

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 14:26:47 CDT 2024