OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Where condition to remove duplicate rows

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Where condition to remove duplicate rows

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

Where condition to remove duplicate rows [message #633116]

Wed, 11 February 2015 03:07

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Hello,
I have Table_1
select * from table_1 ;
number: ****** Name:
1 AA
1 BB
2 CC
3 DD
5 FF
6 DD
6 UU
8 FF
9 SD
9 SS

what is the where condition I need to add to remove any duplicate rows, in the example, I want the select statement to give me the following:
2 CC
3 DD
5 FF
8 FF
you can see that 1,1,6,6,9,9 are removed.
How can I do that

Report message to a moderator

Re: Where condition to remove duplicate rows [message #633119 is a reply to message #633116]

Wed, 11 February 2015 03:48

Michel Cadot
Messages: 68751
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For instance,

Code: [Select all] [Show/ hide]

where id not in (select id from mytable group by id having count(*) > 1)

Code: [Select all] [Show/ hide]

where id in (select id from mytable group by id having count(*) = 1)

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Edit: add other solution]

[Updated on: Wed, 11 February 2015 03:50]

Report message to a moderator

Re: Where condition to remove duplicate rows [message #633120 is a reply to message #633119]

Wed, 11 February 2015 03:53

Michel Cadot
Messages: 68751
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Or you can change the query (not just add a WHERE clause) to:

Code: [Select all] [Show/ hide]

select id, name
from (select id, name, count(*) over (partition by id) cnt from mytable)
where cnt = 1
/

Report message to a moderator

Previous Topic:	Quick Question, Forms from DDL
Next Topic:	ANONYMOUS schema

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 14 07:35:37 CDT 2025