Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate (Oracle 9i)
Duplicate [message #445390] Mon, 01 March 2010 07:41 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi Experts,

I want to delete the duplicate rows in a table without using ROWID.

I have the following data.

SNO  SNAME SECTION
1    RAM     A
2    SHYAM   B
2    SHYAM   B
3    KISHOR  C
3    KISHOR  D
4    RAMESH  E
5    RAJESH  F
5    RAJESH  F

The Output Should be like this.

SNO  SNAME SECTION
1     RAM     A
2     SHYAM   B
3     KISHOR  C
3     KISHOR  D
4     RAMESH  E
5     RAJESH  F    


Please help me.
Thanks in advance.
Re: Duplicate [message #445391 is a reply to message #445390] Mon, 01 March 2010 07:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
FAQ
Re: Duplicate [message #445392 is a reply to message #445390] Mon, 01 March 2010 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Mon, 01 March 2010 08:03]

Report message to a moderator

Re: Duplicate [message #445397 is a reply to message #445390] Mon, 01 March 2010 08:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Other than HOmework, can you give us a reason for avoiding ROWID?
Re: Duplicate [message #445398 is a reply to message #445392] Mon, 01 March 2010 09:05 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
CREATE TABLE table_duplicate(sno NUMBER,sname VARCHAR2(20),section CHAR(1));

INSERT INTO table_duplicate VALUES(1,'RAM','A');
INSERT INTO table_duplicate VALUES(2,'SHYAM','B');
INSERT INTO table_duplicate VALUES(2,'SHYAM','B');
INSERT INTO table_duplicate VALUES(3,'KISHOR','C');
INSERT INTO table_duplicate VALUES(3,'KISHOR','D');
INSERT INTO table_duplicate VALUES(4,'RAMESH','E');
INSERT INTO table_duplicate VALUES(5,'RAJESH','F');
INSERT INTO table_duplicate VALUES(5,'RAJESH','F');
I am
sending the inserted data. Please help me.
Re: Duplicate [message #445400 is a reply to message #445398] Mon, 01 March 2010 09:14 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Is there a reason that you
1. Didn't bother reading the link that Littlefoot posted
2. Ignored Jrowbottom's question?
Re: Duplicate [message #445430 is a reply to message #445390] Mon, 01 March 2010 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Below can be done without ROWID

       SNO SNAME		S   COUNT(*)
---------- -------------------- - ----------
	 1 RAM			A	   1
	 2 SHYAM		B	   2
	 3 KISHOR		C	   1
	 3 KISHOR		D	   1
	 4 RAMESH		E	   1
	 5 RAJESH		F	   2

Re: Duplicate [message #445500 is a reply to message #445390] Tue, 02 March 2010 04:28 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

I have something documented allready for my refrence source i don remember but can paste here the. The article follows

If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:
DELETE FROM pink_slippers
WHERE rowid not in
(SELECT MIN(rowid) 
FROM pink_slippers 
GROUP BY column1, column2, column3...);

Here column1, column2, column3 constitute the identifying key for each record. If the keys cannot be identified for the table, create a temporary table using the query
CREATE TABLE fuzzy_polka_dot_slippers 
AS SELECT DISTINCT * 
FROM old_tattered_shoes; 

Then drop the original table and rename the temp table to original tablename.

This is to share with you i know the question is to do it without a rowid. But why to ignore such a beautiful feature of oracle.

Jak
Re: Duplicate [message #445504 is a reply to message #445500] Tue, 02 March 2010 04:44 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And is this not a solution that is NOT in the FAQ link provided earlier?
Read it before posting, especially method 4.

Your second method implies recreating all grants, recompiling all dependent objects and so on.

Regards
Michel
Previous Topic: UTC Date time should be in format 2010-02-24T17:08:09Z.
Next Topic: which privilege i need
Goto Forum:
  


Current Time: Sat Dec 10 20:35:25 CST 2016

Total time taken to generate the page: 0.18314 seconds