D
I don't know if there's similar functionality in postgre, but SQL Server 2005 lets you do this neat little trick: (Lines starting with -- are comments)
--Before
SELECT * FROM deleteduplicates;
--Show the results of attaching row numbers partitioned by id
WITH [numberedduplicates](row_number, id, name) AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id),
id,
name
FROM deleteduplicates
)
SELECT * FROM numberedduplicates;
--Delete the rows that aren't the first row for a particular id
WITH [numberedduplicates](row_number, id, name) AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id),
id,
name
FROM deleteduplicates
)
DELETE FROM numberedduplicates WHERE row_number > 1;
--After
SELECT * FROM deleteduplicates;
The resulting output:
id name
----------- --------------------------------
1 bob
1 bob
1 bob
2 jane
2 jane
2 jane
3 mary
4 jeff
4 jeff
(9 row(s) affected)
row_number id name
1 1 bob
2 1 bob
3 1 bob
1 2 jane
2 2 jane
3 2 jane
1 3 mary
1 4 jeff
2 4 jeff
(9 row(s) affected)
(5 row(s) affected)
id name
1 bob
2 jane
3 mary
4 jeff
(4 row(s) affected)
I just found out about this one the other day. :)