# Databases > SQL Delete duplicate records without using "rowid"

## Geek_Guest

*Question asked by visitor Joe*

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

----------


## jamesravid

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

----------


## jbanx

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

----------


## Barbie

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

----------


## sutnarcha

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

I think there is no solution without using ROWID.

----------


## vadaliraghu

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



Thanks...

----------


## debasisdas

No, that is not possible in oracle..

----------


## bhaski

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

----------


## sureshkumar.mtech

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


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


Thanks

----------


## nathsambu

I think this is not possible in Oracle.

----------


## amitpatel66

You can try using analytical function to delete duplciates in oracle

----------


## ecearund

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);

----------


## asthutc

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)
)

----------


## asthutc

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)
)

----------


## harishkshetty

Try this  :Smile: 

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

----------


## amitpatel66

@Harishkshetty,

The question is without using a rowid.

----------

