AjayLebaka

• Mar 26th, 2005

 

DELETE TableName WHERE rowid NOT IN ( SELECT MIN(rowid) FROM TableName GROUP BY ColumnName );

j Lehew

• Aug 25th, 2005

 

Use the DISTINCT keyword right after SELECT... 
 
i.e. SELECT DISTINCT customername FROM customer

purushgeek

 Profile Answers by purushgeek 

• Oct 20th, 2005

 

SELECT * from emp where rowid<>(select max(rowid) from emp )

matloob hussain

• Oct 28th, 2005

 

I don;t think the above will work

plese try the following 

Delete from emp a  where a.rowid > ( select min(rowid) from emp b

where a.empno = b.empno group by empno)

Matloob Hussain

richardlonghill

• Jan 20th, 2006

 

Hello,

What you mean eliminate, does it mean to delete the rows or to de-select the duplicate rows..

We can use either distinct or rowid to eliminate duplicate rows

Satish Kumar

• Apr 6th, 2006

 

delete from <table name> a1 where a1.rowid > any (select rowid from <table name> a2 where a1.<col name> = a2.<col name>;

This works....

Regards,                                                                                                              

Satish Kumar

 

anonymous

• Jul 31st, 2006

 

Use distinct keyword.

NARENDRA

• Dec 7th, 2006

 

DELETE FROM EMP E WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM EMP M WHERE E.EMPNO = M.EMPNO);

Ramu

• Jan 5th, 2007

 

there are around 4 ways to eliminate duplicate records upto my knowledge.

the first preferable answer u have to say is :

delete from table_name where rowid not in (select min(rowid) from table_name group by column_name)

if the interviewer asks for another method: try this method.

1. create table_name_b as select distinct * from table_name_a;

2. drop table_name_a;

3. rename table_name_b as table_name_a;

Finished...

and the remaingin methods r confusing where as u will be stopped as soon as u say these 2 types ..

Thanx..

praveenkumarmitta

 Profile Answers by praveenkumarmitta 

• Jan 24th, 2007

 

we can print duplicate rows from a table in the follwoing way.... select * from table_name where count(*) >1; it will display the duplicate rows...

srisiri.venkat

 Profile Answers by srisiri.venkat 

• Apr 22nd, 2008

 

With the help of Distinct function

jnegimail

 Profile Answers by jnegimail 

• Nov 2nd, 2009

 

CREATE TABLE duptest
( Id varchar(5),
nonid varchar(5));

INSERT INTO duptest VALUES('1','a');
INSERT INTO duptest VALUES('2','b');

SELECT * FROM duptest

DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest
GROUP BY id)

emraan

 Profile Answers by emraan 

• Dec 29th, 2009

 

create table emp_1 as select * from emp

insert into emp_1 select * from emp

select * from emp_1

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

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Feb 23rd, 2010

 

delete from emp
where rowid in (
select min(rowid) from emp where sal in (
select sal from emp a group by sal having count(rowid) > 1
)
group by sal)

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Feb 23rd, 2010

 

DELETE FROM emp WHERE rowid NOT IN (SELECT max(rowid) FROM emp
GROUP BY sal)

SujitKr

 Profile Answers by SujitKr 

• Feb 23rd, 2010

 

DELETE FROM <table name> WHERE rowid NOT IN (SELECT max(rowid) FROM <table name> GROUP BY <column name containing repeated datas>);

AS

table name: t

column name: ename,branch

SQL:  DELETE FROM t WHERE rowid NOT IN (SELECT MAX (rowid) FROM t GROUP BY ename,branch)

Sujit

Naveen Kumar39

 Profile Answers by Naveen Kumar39 

• May 3rd, 2011

 

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