Deleting more duplicates

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.On Tue, 25 Jul 2006 10:36:10 GMT, Maury wrote:

Quote:

Originally Posted by

>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|||Hugo Kornelis ha scritto:

Quote:

Originally Posted by

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.|||Maury (maurizio.alberti_TOGLI_@.gmail.com) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx