Deleting more duplicates

[Maury]

Hello,
I have a stored procedure that deletes duplicates
in one table:

.....
AS
BEGIN
DELETE FROM mytable
WHERE Id IN(
SELECT Max(id)
from mytable
group by date, idsens
having count(*) >1
)
END

sometimes it happens that I have >2 rows with duplicated values.
How can I write a new stored procedure that delete all rows with
duplicated infomrations (leaving only one row with those values)?

Thanks
M.A.

[Hugo Kornelis]

On Tue, 25 Jul 2006 10:36:10 GMT, Maury wrote:

>Hello,
I have a stored procedure that deletes duplicates
in one table:

....
AS
BEGIN
DELETE FROM mytable
WHERE Id IN(
SELECT Max(id)
from mytable
group by date, idsens
having count(*) >1
)
END

sometimes it happens that I have >2 rows with duplicated values.
How can I write a new stored procedure that delete all rows with
duplicated infomrations (leaving only one row with those values)?

Hi Maury,

For SQL Server 2005:

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn 1;

For all versions of SQL Server:

DELETE FROM mytable
WHERE EXISTS
(SELECT *
FROM mytable AS m2
WHERE m2.date = mytable.date
AND m2.idsens = mytable.idsens
AND m2.Id < mytable.Id);
--
Hugo Kornelis, SQL Server MVP

[Maury]

Hugo Kornelis ha scritto:

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn 1;

This is very very interesting (I didn't know these commands)
I have to search a good reference
or stored procedure language manual
for sql server 2005
some hints?
thanks!!!!
M.A.

[Erland Sommarskog]

Maury (ma*********************@gmail.com) writes:

This is very very interesting (I didn't know these commands)
I have to search a good reference
or stored procedure language manual
for sql server 2005
some hints?

Books Online. Ships with SQL Server. Update available from the link below.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx