oracle 9i sql question

[tushar.joshi83]

hello,
i want to know that hw to select & delete duplicate rows from a table?

[ozkucur]

in both select and delete, the duplicate rows are processed.

You can use the DISTINCT keyword to surpress the duplicate rows in select.
E.g:

Select DISTINCT last_name from employees;

[richfats]

But how would select distinct know how may duplicate records exist and which particular ones to delete and which to keep. The best approach would be to count and then group the duplicates so say you have data.

a b c
= = =
1 a 1
1 a 1
2 b 2
2 b 2
2 b 2
3 b 3

Select distinct will bring back
SELECT
DISTINCT
a,
b,
c
FROM A

a b c
= = =
1 a 1
2 b 2
3 b 3

but record 3 b 3 is not a duplicate. in this case a group by aggregate e.g.

SELECT
a,
b,
c,
count(1)
FROM a
GROUP BY
a, b, c
having count(1) > 1

will return everything with more than 1 row.

a b c count(1)
= = = =
1 a 1 2
2 b 2 3

You can then formulate you next step using this result. If anyone has a simpler way let me know