Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Where condition to remove duplicate rows
Where condition to remove duplicate rows [message #633116] Wed, 11 February 2015 03:07 Go to next message
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
Re: Where condition to remove duplicate rows [message #633119 is a reply to message #633116] Wed, 11 February 2015 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67286
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

or
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 Go to previous message
Michel Cadot
Messages: 67286
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or you can change the query (not just add a WHERE clause) to:
select id, name
from (select id, name, count(*) over (partition by id) cnt from mytable)
where cnt = 1
/

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


Current Time: Wed Aug 05 11:58:42 CDT 2020