Q:

Delete duplicate rows

I have a table that looks like this

Table1
Id, Name

How can I write a query that delete all rows with duplicate names but keep the one with the lower Id?

A:

If you are using SQL Server 2005 or later:
With Dups As
    (
    Select Id, Name
        , Row_Number() Over ( Partition By Name Order By Id ) As Num
    From Table1
    )
Delete Table1
Where Id In (
            Select Id
            From Dups
            Where Num > 1
            )

If using SQL Server 2000 and prior
Delete Table1
Where Exists    (
                Select 1
                From Table1 As T1
                Where T1.Name = Table1.Name
                Having Min( T1.Id ) <> Table1.Id
                )

