SQL Server Duplicate

Question

I need to find duplicates in a table named lead, which contains around 100k records. The duplicates have similar values in the company column, such as:

The goal is to retain only the latest leadid (95803 in this case). However, there's an issue with leadid 95803, which has some extra characters after a space.

I've attempted to use the following script, but it's not providing the desired results:

select t1.*
FROM [dbo].[LEAD] t1
LEFT JOIN (
    SELECT
        company,
        city,
        MAX(leadid) AS keep_leadid
    FROM [dbo].[LEAD]
    GROUP BY company, city
) t2 ON t1.company = t2.company AND t1.city = t2.city
WHERE t1.leadid <> t2.keep_leadid 
  AND t1.company LIKE '%Uvalde Country%'

Any assistance in refining the script to achieve the intended outcome would be greatly appreciated.

I want to all delete except of this:

There are many companies, with different strings, I want to apply same script for all.

Answer 1

Here's an attempt with the caveat that you are interested in the shortest company names and will be matched with companies that START with the shortest company name, also NOT considering city:

declare @t table([sid] int not null identity(1,1), leadid int, company varchar(80));

insert into @t values(1,'company A');
insert into @t values(30,'company A INC');
insert into @t values(5,'company B');
insert into @t values(9,'company C');
insert into @t values(48,'company C INC');

--query to see join on companies that start with the same string
select *
from 
    @t a
    LEFT join @t b on a.company = left(b.company, len(a.company))

;WITH CTE AS
(
    --get max id per company
    select MAX(case when a.leadid > b.leadid then a.leadid else b.leadid end) max_id, 
    case when len(a.company) < len(b.company) then a.company else b.company end company,
    case when len(a.company) < len(b.company) then len(a.company) else len(b.company) end len_company
    from 
        @t a
        inner join @t b on a.company = left(b.company, len(a.company))
    group by --group by shortest company?
        case when len(a.company) < len(b.company) then a.company else b.company end,
        case when len(a.company) < len(b.company) then len(a.company) else len(b.company) end
), CTE2 AS
(
    --ROW NUMBER ON SHORTEST COMPANIES TO DISCARD ONES WITH MORE CHARACTERS AT THE END
    select max_id, MAX(company) company, ROW_NUMBER() OVER(PARTITION BY max_id ORDER BY MIN(len_company)) rn
    from CTE
    group by max_id, len_company
)
SELECT max_id, company
FROM 
    CTE2 
where
    rn = 1;