kalavani

• Jul 25th, 2011

 

redundant means repeatedly.so we take one time others negligible.because redundant affect the answer part

AnithaLetchumi

 Profile Answers by AnithaLetchumi 

• Jul 29th, 2011

 

With the usage of rowid or ranking we can achieve deleting redundant rows in the table having no key or any distinct columns.

Code
CREATE TABLE dup(n1 int,val1 VARCHAR2(10));


 


INSERT INTO dup VALUES ( 1, 'x');


INSERT INTO dup VALUES ( 1, 'y');


INSERT INTO dup VALUES ( 1, 'x');


INSERT INTO dup VALUES ( 1, 'y');


INSERT INTO dup VALUES ( 1, 'z');


INSERT INTO dup VALUES ( 1, 'y');


INSERT INTO dup VALUES ( 2, 'xx');


INSERT INTO dup VALUES ( 2, 'xx');


INSERT INTO dup VALUES ( 2, 'zz');


INSERT INTO dup VALUES ( 2, 'zz');


 


SELECT * FROM dup;


 


DELETE FROM dup d1 WHERE d1.ROWID >


ANY (SELECT d2.ROWID FROM dup d2 WHERE d1.n1=d2.n1 AND d1.val1=d2.val1);

dinesh.smhdr

 Profile Answers by dinesh.smhdr 

• Sep 13th, 2011

 

delete from emp where rowid not in (select min(rowid) from emp group by empno);

Here all the records having max(rowid) will be deleted and records with min(rowid) will be left behind giving unique records.

Allan Hunter

 Profile Answers by Allan Hunter 

• Sep 21st, 2011

 

You're question is vague, but from what I understand it to mean is to remove duplicate rows wihle keeping the columns in tact. The easiest way I know is to make your query based on what makes the data unique, and make your query with that in mind. For example, in table Emp, you have employee and in table payroll you have you have their work schedule and their salary information. You want to know the employee's name, their title,their weekly hours and gross pay. But, because there's a one-to-many correlation you have to find a way to make the data only appear once. I think this is how:

SELECT DISTINCT EMP.NAME, EMP.TITLE, PAYROLL.WHOURS, PAYROLL.ANNSAL
FROM EMP.EMPNO = PAYROLL.EMPNO
ORDER BY EMP.NAME

smonroy

 Profile Answers by smonroy 

• Sep 27th, 2011

 

Hi my Friend this is the best way to you issue

DELETE FROM table_name where camp1 in (SELECT camp1 FROM table_name GROUP BY camp1 HAVING Count(camp1)>1)

Example:

DELETE FROM ubication where serial_number in
(SELECT serial_number FROM ubication
GROUP BY serial_number HAVING Count(serial_number)>1)