How to find duplicate rows. [message #333141] |
Thu, 10 July 2008 10:28  |
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 #333290 is a reply to message #333211] |
Fri, 11 July 2008 02:49   |
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   |
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   |
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  |
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.
|
|
|