Home » SQL & PL/SQL » SQL & PL/SQL » Extract duplicate records. (Oracle 9.7.0)
Extract duplicate records. [message #439987] |
Wed, 20 January 2010 08:12  |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
I have following set of data,
Code Name Price
101 A 10
102 B 15
102 B
103 C 5
104 C 5
105 D 7
105 D 7
106 E
106 E
In the above data set, If the I have Name and Price in two records is duplicated I need to get Code, Name and Price
So the Output I require is
Code Name Price
103 C 5
104 C 5
105 D 7
106 E
Can anybody help me to get this o/p.
Brayan
|
|
|
|
|
|
Re: Extract duplicate records. [message #439994 is a reply to message #439987] |
Wed, 20 January 2010 08:39   |
 |
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
Try this:
WITH MyData AS (
SELECT 101 Code, 'A' Name, 10 Price FROM DUAL UNION ALL
SELECT 102, 'B', 15 FROM DUAL UNION ALL
SELECT 102, 'B', null FROM DUAL UNION ALL
SELECT 103, 'C', 5 FROM DUAL UNION ALL
SELECT 104, 'C', 5 FROM DUAL UNION ALL
SELECT 105, 'D', 7 FROM DUAL UNION ALL
SELECT 105, 'D', 7 FROM DUAL UNION ALL
SELECT 106, 'E', null FROM DUAL UNION ALL
SELECT 106, 'E', null FROM DUAL )
SELECT DISTINCT d.* FROM (
SELECT Name, NVL(Price,0) Price, COUNT(*) cnt
FROM MyData t
GROUP BY Name, Price) g, MyData d
WHERE cnt > 1
AND d.Name = g.Name
AND NVL(d.Price,0) = g.Price
ORDER BY 2,1;
[Updated on: Wed, 20 January 2010 08:45] by Moderator Report message to a moderator
|
|
|
Re: Extract duplicate records. [message #440018 is a reply to message #439987] |
Wed, 20 January 2010 11:47   |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Michel,
Oracle version is 9.2.0.7
Create table test(Code varchar2(5), Name varchar2(5), Price number(3));
Insert into test values('101','A',10);
Insert into test values('102','B',15);
Insert into test values('102','B',Null);
Insert into test values('103','C',5);
Insert into test values('104','C',5);
Insert into test values('105','D',7);
Insert into test values('105','D',7);
Insert into test values('106','E',Null);
Insert into test values('106','E',Null);
The o/p I'm expecting is
In the above data set, If the Name and Price in two more ore records is duplicated I need to get Code, Name and Price of those records.
Code Name Price
103 C 5
104 C 5
105 D 7
106 E
Brayan
|
|
|
|
Re: Extract duplicate records. [message #440025 is a reply to message #439987] |
Wed, 20 January 2010 12:37   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
So there are three basic solutions to this problem as have been indicated in this thread so far.
1) the traditional solution is use of a simple COUNT(*) and GROUP BY/HAVING to select those rows that have more than one entry based on the "UNIQUENESS" criteria. The result is used as an in list subquery to get the final answer. This is the LKBrwn_DBA solution. Too bad he didn't take a little time to indent his query for easier reading. Beacuse this solution was possible prior to the introduction of analytics, and because most oracle developers don't know what analytics are yet, this soultion is the one you will seen in most oracle systems and the one most people would think of first.
2) the advanced solution is the analytic solution. Basically the same kind of thing as the traditional solution except that you exchange an analytic for a subquery. This is the pablolee solution. It is much cleaner in terms of lines of code. It would be interesting for someone to do a performance comparison of the two alternatives.
3) the third solution is a correlated subquery (aka. exists) version of the query. This third solution however tends to run slower that the other two and given your need to treat nulls as actual data, could perform very poorly. There are however some situations where this solution could perform well, particularly the scenario wherein the data set is very large, UNIQUE columns are NOT NULL, index exists on the UNIQUE columns, and the number of duplicates is small compared to the full data set.
As an aside, the interesting thing for me about this kind of problem is that it requires two passes of the data no matter how you slice/dice it and thus the performance of any solution will depend upon how you manager those two passes. This is why my money is on the analytic solution as performing best in most scenarios, especially when the dataset is large. Indeed, it is a good mental exercise to visualize in your head how these three solutions manipulate the data differently to arrive at the same answer.
Good luck, Kevin
|
|
|
Re: Extract duplicate records. [message #440436 is a reply to message #440018] |
Sun, 24 January 2010 16:51   |
shahidmughal
Messages: 91 Registered: January 2006 Location: Faisalabad Pakistan
|
Member |

|
|
hi,
i hope you are fine.
i saw your post very late because of my absence for a long time.
but i have a solution for your problem.
Use the following Query.
your data
==========
ICODE INAME PRICE
---------- ---------- ----------
101 A 10
102 B 15
102 B
103 C 5
104 C 5
105 D 7
105 D 7
106 E
106 E
=========================================
Applied Query
============
select distinct icode,iname,price
from abc
where (iname,nvl(price,0)) in(select iname,nvl(price,0)
from (select iname,nvl(price,0),count(1)
from abc
group by iname,price
having count(1) >1));
=============================================
Finally The Result
==================
ICODE INAME PRICE
---------- ---------- ----------
103 C 5
104 C 5
105 D 7
106 E
I hope this will solve your problem.
Regards
Muhammad Shahid Mughal
Oracle Developer / Managing Director
Professional's Corner ( Software Development Department )
Faisalabad Pakistan
Mobile No : +92 321 664 1931
|
|
|
Re: Extract duplicate records. [message #440438 is a reply to message #440436] |
Sun, 24 January 2010 16:59   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
SELECT DISTINCT icode,
iname,
price
FROM abc
WHERE (iname,Nvl(price,0)) IN (SELECT iname,
Nvl(price,0)
FROM (SELECT iname,
Nvl(price,0),
Count(1)
FROM abc
GROUP BY iname,
price
HAVING Count(1) > 1));
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
|
|
|
|
Re: Extract duplicate records. [message #440458 is a reply to message #440436] |
Mon, 25 January 2010 00:59   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
shahidmughal wrote on Sun, 24 January 2010 23:51
select distinct icode,iname,price
from abc
where (iname,nvl(price,0)) in(select iname,nvl(price,0)
from (select iname,nvl(price,0),count(1)
from abc
group by iname,price
having count(1) >1));
=============================================
Finally The Result
==================
ICODE INAME PRICE
---------- ---------- ----------
103 C 5
104 C 5
105 D 7
106 E
I hope this will solve your problem.
Regards
Muhammad Shahid Mughal
Oracle Developer / Managing Director
Professional's Corner ( Software Development Department )
Faisalabad Pakistan
Mobile No : +92 321 664 1931
I believe you tampered with the outcome. The query you provided contains an error: since you did not select price in the innermost query, you could not select nvl(price,0) in the one surrounding it.
|
|
|
Re: Extract duplicate records. [message #440471 is a reply to message #439987] |
Mon, 25 January 2010 02:36   |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
Thanks everybody. Using following code I could get the o/p.
SQL> select distinct code, name, price
2 From (
3 select code, name, price, count(*) over (partition by name, price order by name, price) cnt
4 from test
5 )
6 Where cnt > 1;
CODE NAME PRICE
----- ----- ---------
103 C 5
104 C 5
105 D 7
106 E
Above code is very fast compared with the traditional group by and having caluse, as my table contains 35000000 records
Regards,
Brayan.
|
|
|
|
|
|
Re: Extract duplicate records. [message #440510 is a reply to message #440508] |
Mon, 25 January 2010 05:30   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:COUNT(1) can reduce the memory uses.
This is completely wrong.
Search on the forum, there have many topics with this discussion and proofs have been produced.
SQL> l
1 SELECT code,
2 name,
3 price
4 FROM (SELECT code,
5 name,
6 price,
7 COUNT(1) over(PARTITION BY name, price) cnt
8 FROM test) temp
9* WHERE cnt > 1
SQL> /
CODE NAME PRICE
----- ----- ----------
103 C 5
104 C 5
105 D 7
105 D 7
106 E
106 E
SQL> l
1 SELECT distinct code,
2 name,
3 price
4 FROM (SELECT code,
5 name,
6 price,
7 COUNT(1) over(PARTITION BY name, price) cnt
8 FROM test) temp
9* WHERE cnt > 1
SQL> /
CODE NAME PRICE
----- ----- ----------
104 C 5
105 D 7
103 C 5
106 E
4 rows selected.
Seems with and without distinct you do not get the same answer.
kuldeepkumar1999 would you like to add something informative in this?
Regards
Michel
|
|
|
Re: Extract duplicate records. [message #440511 is a reply to message #439987] |
Mon, 25 January 2010 05:33   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You are wrong, on both counts.
Testing your solutions is always an idea:
SQL> SELECT code,
2 name,
3 price
4 FROM (SELECT code,
5 name,
6 price,
7 COUNT(1) over(PARTITION BY name, price) cnt
8 FROM test) temp
9 WHERE cnt > 1;
CODE NAME PRICE
----- ----- ----------
103 C 5
104 C 5
105 D 7
105 D 7
106 E
106 E
6 rows selected.
I see duplicates, hence the need for distinct.
And oracle converts count(1) to count(*) internally so the idea that count(1) would be an improvement is false as well.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:07:58 CST 2025
|