How to delete duplicate rows from a table

[athermaliq]

Hi friends,
can any body please tell me how can i delete duplicate records from a table .
If the table I have is with the following data

SNO SNAME
--------------------------------
1001 ATHER
1001 ATHER
1001 ATHER
1001 ATHER
1001 ATHER

is it possible to delete 4 records and keep any one record, plese reply if someone has answer.

Thanx& Regards
Ather

[kevinsmith]

Hi Ather,
Assuming the table name as TEST with data in it as.

SNO SNAME
---- ---------
1001 ATHER
1001 ATHER
1001 ATHER
1001 ATHER
1001 ATHER

Query to delete duplicate records can be written as given below

SQL> delete from test a
where rowid <> ( select max(rowid)
from test b
where a.sno = b.sno
and a.sname = b.sname )

Hope it helps

Thnx&Regards
Kevin

[silpi jain]

delete from tablename e1 where rowid>(select min(rowid) from tablename e2
where e1.code=e2.code);

[parult]

Hi,

Try this out

SELECT sno,
COUNT(sno) AS NumOccurrences
FROM <tblname>
GROUP BY sno
HAVING ( COUNT(sno) > 1 )

Originally Posted by athermaliq

Hi friends,
can any body please tell me how can i delete duplicate records from a table .
If the table I have is with the following data

SNO SNAME
--------------------------------
1001 ATHER
1001 ATHER
1001 ATHER
1001 ATHER
1001 ATHER

is it possible to delete 4 records and keep any one record, plese reply if someone has answer.

Thanx& Regards
Ather

[sriharid]

delete from test where rowid not in (select min(rowid) from test
group by sno sname);

[harsha1780]

hi guys, i would like to know how to write a query to delete duplicate rows from the below table, friends reply me pls

KNO KNAME
---- -------
555 ssss
333 dddd
555 ssss
333 dddd
555 ssss
333 dddd