subrahmanaym

• Jun 13th, 2005

 

Using rowid we can eliminate duplicate rowa from  
the table...

balkrishna solanki

• Aug 10th, 2005

 

DELETE (or SELECT *) FROM TABLE1 TABLE1_ALIAS1 
WHERE EXISTS (SELECT 1 FROM TABLE1 TABLE1_ALIAS2 
WHERE TABLE1_ALIAS1.FIELD1 = TABLE1_ALIAS2.FIELD1 
AND TABLE1_ALIAS1.ROWID < TABLE1_ALIAS2.ROWID) 
ORDER BY FIELD1; 

Jasjeet Singh

• Aug 10th, 2005

 

delete from table where (duplicate value field,rowid) not in (select duplicate value field,max(rowid) from table group by duplicate_value_field) 

santhi

• Nov 23rd, 2005

 

DELETE FROM table_name A  WHERE ROWID > ( SELECT min(rowid) FROM table_name B           WHERE A.key_values = B.key_values);

or 

 one can create a table2 from the existing table1 by selecting distinct values and

 dropping the table1. 

gannesunil

 Profile Answers by gannesunil 

• Aug 2nd, 2006

 

select * from <table_name> where rowid not in(select max(rowid) from

<table_name> group by<column_names>);

srinivas vadlakonda

• Oct 24th, 2006

 

delete from emp a whre rowid in (select min(rowid) from emp b where a.empno=b.empno);

neemachawhan

 Profile Answers by neemachawhan 

• Oct 29th, 2009

 

Assume there is an emp table with 3 columns (ename, sal, dept)

DELETE FROM emp
WHERE rowid > ANY (SELECT rowid
FROM emp e2
WHERE e2.ename = e1.ename
AND e2.sal = e1.sal
AND e2.dept = e1.dept);

OR

DELETE FROM emp a

WHERE EXISTS (
SELECT 'true'
FROM emp b
WHERE b.emp_id = a.emp_id
AND b.rowid < a.rowid
);

shiiva

 Profile Answers by shiiva 

• Feb 4th, 2011

 

Delete the Records selected from the below query:

select a.*
from emp1 a, emp1 b
where a.empno = b.empno
and a.rowid < b.rowid 

shiv

Dhiraj

• Aug 29th, 2011

 

select distinct * from table_name