SQL Server - Transfering / Adopting only one name from Rows with same ID´s but different names

Question

I have the following problem:
My table looks like this.

   ID    Name
   1     Company LTD.
   1     Company Limited
   1     Company ltd
   2     Example Corp.
   2     Example Corporation
   ...

Since they are "different" names for the same company, I just decided to keep the longest name as my company name.

So my question is. How do I check for the longest one and at the same moment just keep one entry, e.g.

   ID    Name
   1     Company Limited
   2     Example Corporation

The table should look like this afterwards.

Answer 1

You can do this with a ROW_NUMBER() with a PARTITION on the ID and ordering by the LEN() desc:

;With Cte As
(
    Select  *,
            Row_Number() Over (Partition By Id Order By Len(Name) Desc) As RN
    From    YourTable
)
Delete  Cte
Where   RN <> 1

Note: This will physically remove the records from your table that are not the longest entry. If you do not wish to physically remove them, and only SELECT the longest entries, use the following instead:

;With Cte As
(
    Select  *,
            Row_Number() Over (Partition By Id Order By Len(Name) Desc) As RN
    From    YourTable
)
Select  Id, Name
From    Cte
Where   RN = 1