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 Go to next message
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 #439988 is a reply to message #439987] Wed, 20 January 2010 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
distinct?
Re: Extract duplicate records. [message #439991 is a reply to message #439987] Wed, 20 January 2010 08:15 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Use COUNT in its ANALYTIC form (Find the syntax @ http://tahiti.oracle.com)
Re: Extract duplicate records. [message #439993 is a reply to message #439987] Wed, 20 January 2010 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Well, can you tell us where you found a 9.7 Oracle version?

Also, if you post a working Test case: create table and insert statements along with the result you want with these data, we can work with your table and data and show you.

Regards
Michel
icon12.gif  Re: Extract duplicate records. [message #439994 is a reply to message #439987] Wed, 20 January 2010 08:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #440019 is a reply to message #439987] Wed, 20 January 2010 11:49 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried LKBrwn_DBA's query?
Re: Extract duplicate records. [message #440025 is a reply to message #439987] Wed, 20 January 2010 12:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #440441 is a reply to message #440438] Sun, 24 January 2010 18:03 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

Sorry Friend
I will take care next time.
Re: Extract duplicate records. [message #440458 is a reply to message #440436] Mon, 25 January 2010 00:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
icon14.gif  Re: Extract duplicate records. [message #440497 is a reply to message #439987] Mon, 25 January 2010 03:54 Go to previous messageGo to next message
kuldeepkumar1999
Messages: 2
Registered: January 2010
Junior Member
hi,

you can try this.

SELECT code,
name,
price
FROM (SELECT code,
name,
price,
COUNT(1) over(PARTITION BY name, price) cnt
FROM faq) temp
WHERE cnt > 1;

Re: Extract duplicate records. [message #440503 is a reply to message #440497] Mon, 25 January 2010 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Welcome to the forum.

Can you tell us in what your query is different to the one in the previous post?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Extract duplicate records. [message #440508 is a reply to message #439987] Mon, 25 January 2010 05:14 Go to previous messageGo to next message
kuldeepkumar1999
Messages: 2
Registered: January 2010
Junior Member
Hi Michel Cadot,

If I am not wrong, There is no need of DISTINCT keyword, and COUNT(1) can reduce the memory uses.

Mr. Michel would you like to add something informative in this?

Re: Extract duplicate records. [message #440510 is a reply to message #440508] Mon, 25 January 2010 05:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Extract duplicate records. [message #440519 is a reply to message #440511] Mon, 25 January 2010 06:04 Go to previous message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Just adding some thing to cooki....
Count(*) and count(1)

sriram Smile
Previous Topic: help required in SQL date condition (merged 3)
Next Topic: sql query problem
Goto Forum:
  


Current Time: Sat Feb 08 07:07:58 CST 2025