Delete duplicate records without using "rowid"

[B]Question asked by visitor Joe[/b]

Hi all! Good Evening.

I would like to know if it is possible to delete duplicate records from a table with out using "rowid" using MYSQL only. Can use "rownum". Plz help me in this regard.

Thanx in advance.
Bye

I think you can't do that. Because Rownum is not linked with any record.

One example is here
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

delete from t1
where col1=1 and col2=1

Jaiprakash,
we need to delete only one of those duplicated rows.
but that delete statement will deletes both.:confused:

Yes, it deletes both 1st and 2nd records.

I think there is no solution without using ROWID.

Try this....
delete from table where rowid=(select max(rowid) from table group by dup_field_name having count(*) > 2)



Thanks...

No, that is not possible in oracle..

hi
use rank() function to delete these duplicate records in oracle

[QUOTE=bhaski;14848]hi
use rank() function to delete these duplicate records in oracle[/QUOTE]

could you please explain with example query.................


Thanks

I think this is not possible in Oracle.

You can try using analytical function to delete duplciates in oracle

Yes it is possible using analytic functions...
I think this one will help you...

select *
from ( select a.*,
row_number() over ( partition by column1 order by column2) r
from table a)
where r >1;

delete from table where (c1,c2,...) in
(Select * from table);

Yeah, It's possible in SQL Server 2005
try this...
This query delete duplicate records in one shot....

delete from Table_Name where Column_Name in
(
select Column_Name from Table_Name
group by Column_Name
having (count(Column_Name)>1)
)

Yeah, It's possible in SQL Server 2005
try this...
This query delete duplicate records in one shot....

delete from Table_Name where Column_Name in
(
select Column_Name from Table_Name
group by Column_Name
having (count(Column_Name)>1)
)

Try this :)

delete from employees emp1 where rowid < (select max(rowid) from employees emp2 where emp1.employee_id=emp2.employee_id);

@Harishkshetty,

The question is without using a rowid.