Duplicate Rows

Hi,

I want to delete duplicate rows from a table using rownum.

If I use this command

Delete from mytable where rownum > 1 (to delete all rows except rownum = 1) nothing is getting deleted.(There are 2 similar rows in mytable)

But If I use following command

Delete from mytable where rownum < 2 then row with rownum = 1 is getting deleted.

A bit confused why the first sql is not working while the second sql is working fine.

Regards
Sudhakar

while using rownum in DML statement '>' is invalid, u have to use only '<' less than operator

Only this SQL query works

Delete from mytable where rownum < 2 then row with rownum = 1 is getting deleted

Hello Sir,

My confusion is if one operator '<' is working then why not '>'..??How come use of '>' is invalid whereas it is valid to use '<'.Can I get some more information/reding references for the same?

Regards

The rownum has one side effect. You cannot select a row number that is greater than a particular number. For example, if you want all but the first two rows and you execute the following statement:

SELECT ROWNUM, Empno, ename
FROM emp
WHERE ROWNUM > 2;
you receive the following message:

no rows selected

The reason is that when the first row is returned, it is given the ROWNUM value of 1. It is tested against the WHERE clause and then rejected. The next row is then given the ROWNUM value of 1. It is tested and also rejected. This continues until no more rows are in the table, and no rows are returned in the result set.

ROWNUM is a psuedocolumn , the value is generated when you select the records, therefore you cannot get values for the> [ greater than ]clause.

regards

Hi

In relational database you cannot use rownum to eliminate duplicates..You should be using rowids...

regards
Hrishy

Hi All,

Thanks for your help.The clarifications/suggestions are very useful to clear my doubts.

Regards

Sudhakar

Hi Sudhakar,

See this is how u can delete duplicate rows from the table:

To delete duplicate rows from this table, use the following query:
delete from empl a where rowid > (
select min(rowid) from empl b where
a.desc = b.desc and
a.emplid = b.emplid);


Vineet