OraFAQ Forum: SQL & PL/SQL » Duplicate Rows

Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Rows (19C)

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

Duplicate Rows [message #687819]

Wed, 21 June 2023 05:59

deepakdot
Messages: 89
Registered: July 2015

Member

Hi ,

here i Have a scenario. Sample is given below.

COL1 COL2
---- ----
1     A
1     A
2     B
2     B
2     C
2     C

Col1 can be associated with Only one Value in Col2. Col1:1 have Only one value A in Col2. So its Good Data. There will be multiple rows for value 1.

Now Col1:2 is having values B and C associated in Col2. This is a Bad data. So I want to retrieve the value 2 from a SQL where it has multiple values in Col2. as there will be multiple rows for each value, I am not able to using group by having count > 1.

Any other way I can find the value 2 from a SQL.

Thanks
Deepak

Report message to a moderator

Re: Duplicate Rows [message #687820 is a reply to message #687819]

Wed, 21 June 2023 06:11

deepakdot
Messages: 89
Registered: July 2015

Member

I am able get the required value as Below. But this table is having huge number of Rows. Distinct is not efficient.

select COL1, COUNT(*) from (
select DISTINCT COL1,COL2 from Table1 ) T
group by COL1 having count(*) > 1;

Any other Way to get the result?

Regards,
Deepak

Report message to a moderator

Re: Duplicate Rows [message #687821 is a reply to message #687820]

Wed, 21 June 2023 06:52

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

A useful general rule is that DISTINCT can often be replaced with EXISTS. Whether that is more or less efficient is a different matter. In your example, one could start from this:

select * from table1 a where exists(select 1 from table1 b where b.col1=a.col1 and b.col2 <> a.col2);

Report message to a moderator

Re: Duplicate Rows [message #687822 is a reply to message #687819]

Wed, 21 June 2023 08:03

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select col1 
from table1
group by col1
having count(distinct col2) > 1
/

Report message to a moderator

Re: Duplicate Rows [message #687904 is a reply to message #687822]

Tue, 18 July 2023 20:01

mathguy
Messages: 107
Registered: January 2023

Senior Member

Can COL2 be NULL? If so, how should that be treated? Suppose you have this additional data:

COL1   COL2
----   ----
   3      4
   3   null
   3   null
   3      4

Is this bad data, or is it OK?

Note that some (if not all) the solutions offered so far seem to ignore this question.

Report message to a moderator

Previous Topic:	Question on Pivot columns
Next Topic:	Finding initial, final, in_between, and initial_and_final events

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 09:17:21 CDT 2024