delete duplicate rows

What's the query for identifying and deleting duplicate rows in a table.
For eg if i have 10 similar rows in a table,then to identify them and deleting 9 rows out of 10.
Thanks in advance
Sumeet Mittal

try this
delete from testtable a
where exists
(select 1 from testtable b
where b.dup_value = a.dup_value
and b.rowid > a.rowid)
Regards
Beksy

Detecting duplicates
I'll show you two of the most useful SQL queries you'll ever learn. (Shh! Don't tell everybody!)
First assume a table with the following data. (No key fields.)

To find duplicates, use the following query.

To use this query in the more general case, just modify the list of fields in the GROUP BY clause (giving careful thought to key vs. nonkey fields and how you're defining a "duplicate").
To see the entire row for each of these "duplicates," just nest the above query:

For our present data, this query isn't particularly interesting because our table only has 1 field. It becomes more useful when you're dealing with rows that match on some fields but not others.
Deleting duplicates
Another way of "deleting" duplicates is to insert into a temp table, then rename the table.

Give careful thought to what fields are in the select (distinct) list because it will affect what's considered a duplicate.