To remove repeated data from table?

I have 2 or more records containing same data
For instance
SNO NAME AGE
1001 ANAND 21
1001 ANAND 21

How do i delete only one row from the table?

use distinct keyword.. it may help

DISTINCT keyword can be used only to query for unique records. it wont delete repeated records.

@anand
distinct word can't be used, i wan record 2 be deleted...

Don't remember exactly,but I think join command would help!

I don't think its join.. I think its UNION... But that shud be between 2 tables rite..

No clue! I vaguely recall self-join in combination with something!

I recall that it can be done using cursor concept..

delete from <tablename> where rownum=1;

@urvershi
it s not working?? it jus deletes 1st record.

create table newtable as select distinct * from oldtable;

here the new table will contain the distinct values ...

@anand
I don't wan 2 create new table, frm de existin table i wan 2 delete it...

@dinesh
do u remember the command?

SQL> select rownum,sno,name,age from anan8;

ROWNUM SNO NAME AGE
---------- ---------- ---------- ----------
1 1001 anand 21
2 1001 anand 21

;;; now u can delete watever row u want to delete.....
here rownum s default field..

SQL> delete from anan8 where rownum = 1;

1 row deleted.

SQL> select * from anan8 ;

SNO NAME AGE
---------- ---------- ----------
1001 anand 21

@:it works urvershi....

it jus deletes a row. even it s not working for all row numbers???
the question is to delete all the repeated rows. how do i know the exact row numbers of repeated data if i have 5k records.

@anand
How do we know the row number of repeated rows?

delete from tablename where row = 12;

@above
how do yo know tat which row is repeated in table?
I jus wan 2 delete all repeated rows...

Guys I Hope This will work....
For having this work done you need to choose the column by which the rows differ for eg,

no name addr
12 Raja chennai
12 Raja chennai
13 Raja chennai
14 Raja chennai

The only thing is you have to mention whether you have check the similarity using particular column or all the column

mention all the column name in the below command if you want to check repetition based on all the column or else mention only the column names based on which you like to check repetation of data...

the command is,

sql> delete <tablename>
where rowid not in (
Select Min(Rowid) from <tablename> group by column1,column2,....columnN)

The above command will delete all the repeating datas in the table

sql> delete <tablename>
where rowid not in (
Select Min(Rowid) from <tablename> group by column1,column2,....columnN)

it works perfectly.

yeaaa mahee it works perfectly...
thanx man... kudos...

delete from table_name tb_var1 where rowid not in (select min(rowid) from table_name tb_var2 where tb_var1.name=tb_var2.name);

try this.. it must work..

@abi
The above command works if we check for all the column using 'and' operator instead of checking only with 'name' column

If we check only 'name' column it will delete all the data with same name, without checking other column...
for eg,
Two employee may have same name and different id.. If use ly name then it will delete 2nd employee with different id..

So if we use this command we have to check for every column....

delete <tablename> <table aliase1> where
<tablealiase1>.rowid < any
(select <tablealiase2>.rowid from <tablename> <tablealiase2>
where
<tablealiase1>.<columnname1>=<tablealiase2>.<columnname1> and <tablealiase1>.<columnname2>=<tablealiase2>.<columnname2> and ......
.... and <tablealiase1>.<columnnamen>=<tablealiase2>.<columnnamen>;