Home » SQL & PL/SQL » SQL & PL/SQL » How to find duplicate rows. (Oracle 9i)
How to find duplicate rows. [message #333141] Thu, 10 July 2008 10:28 Go to next message
deepayan
Messages: 51
Registered: December 2005
Member
Hi All ,

I have a table which may contain identical rows. I need to find identical rows using a SQL query.Is it possible?

NULL values should be treated as identical.

Thanks,
Deepayan
Re: How to find duplicate rows. [message #333143 is a reply to message #333141] Thu, 10 July 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In the last 10 years there were about 12685 persons that ask this question.

Regards
Michel
Re: How to find duplicate rows. [message #333144 is a reply to message #333141] Thu, 10 July 2008 10:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


I am sorry to see that both GOOGLE & the SEARCH function on this forum are broken while repairs are being made.
More/better responses will follow.

[Updated on: Thu, 10 July 2008 10:32] by Moderator

Report message to a moderator

Re: How to find duplicate rows. [message #333145 is a reply to message #333141] Thu, 10 July 2008 10:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Post what you have tried so far.

Search for NVL in oracle reference manual.

Regards

Raj
icon10.gif  Re: How to find duplicate rows. [message #333211 is a reply to message #333141] Thu, 10 July 2008 18:49 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

You are inserting records from TABLE B into TABLE A, and the primary key on both tables is COLUMN A and COLUMN B. Issue the following query to find the duplicate records:-


SELECT * FROM TABLE_B

WHERE (COLUMN_A||COLUMN_B) IN

      (SELECT COLUMN_A||COLUMN_B

       FROM TABLE_A);
Re: How to find duplicate rows. [message #333265 is a reply to message #333211] Fri, 11 July 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't think so as 'A'||'BC' is the same as 'AB'||'C' without being duplicate.

Regards
Michel
Re: How to find duplicate rows. [message #333290 is a reply to message #333211] Fri, 11 July 2008 02:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why concatenate?
Why not use this:
SELECT * FROM TABLE_B

WHERE (COLUMN_A,COLUMN_B) IN

      (SELECT COLUMN_A,COLUMN_B

       FROM TABLE_A);

Anyway, it's not an answer to the OP's question, which only involved a single table.
Re: How to find duplicate rows. [message #333384 is a reply to message #333141] Fri, 11 July 2008 07:59 Go to previous messageGo to next message
mfinn
Messages: 9
Registered: July 2008
Location: UK
Junior Member
I'd probably use something like this (note that rows 8 and 9 contain nulls) :

SQL> SELECT * FROM FREDDY2;

         Z          X
---------- ----------
         1          9
         1          9
         2          9
         3          9
         3          9
         4          9
         4          7



9 rows selected.

SQL> SELECT COUNT(*),F.Z,F.X
  2    FROM FREDDY2 F
  3   GROUP BY F.Z,F.X
  4  HAVING COUNT(*) > 1
  5  ;

  COUNT(*)          Z          X
---------- ---------- ----------
         2
         2          1          9
         2          3          9

SQL> 
Re: How to find duplicate rows. [message #333715 is a reply to message #333141] Mon, 14 July 2008 04:10 Go to previous messageGo to next message
wangfans
Messages: 7
Registered: July 2008
Location: PRC
Junior Member
SQL> desc test_bk;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(10)

select 
* 
from test_bk a 
where  exists 
( select * 
   from test_bk b  
  where a.rowid<>b.rowid 
   and a.col1=b.col1 
   and a.col2=b.col2
)
/


[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Mon, 14 July 2008 04:44] by Moderator

Report message to a moderator

Re: How to find duplicate rows. [message #333724 is a reply to message #333715] Mon, 14 July 2008 04:47 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
@wangfans:

Welcome to the forum; so nice to see another new member contributing to questions.
Please take the time to read our Forum Guidelines. They contain a number of rules we try to uphold to keep OraFAQ tidy and professional.
In the Forum Guidelines there is a section about formatting your posts. It shows you how to keep posts containing code readable. I changed two of your posts now; in the future please do so yourself.
Previous Topic: Table deletion
Next Topic: Find list of proccedures having rule hint
Goto Forum:
  


Current Time: Mon Feb 17 19:07:16 CST 2025