CODEWITHSUNDEEP
sqlsql-serverduplicates
Brandon Shelton
Brandon Shelton

Reputation: 13

SQL Statement to Remove Duplicates Based on a Specific Field

I have a table named people and it looks like this:

Name    Type    Amount
allen   admin   300
brandon admin   150
brandon mrr     25
jared   admin   225
john    mrr     30
john    admin   500
tyler   admin   245

If there is a Name value that has both Type values of 'admin' and 'mrr', I want to use SQL to only keep the 'mrr' value of that Name so that my resulting output looks like this:

Name    Type    Amount
allen   admin   300
brandon mrr     25
jared   admin   225
john    mrr     30
tyler   admin   245

I'm working in SQL Server. Thanks in advance.

Upvotes: 1

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method is to select all mrrs and then only admins that don't match:

select p.*
from people p
where p.type = 'mrr'
union all
select p.*
from people p
where p.type = 'admin' and
      not exists (select 1 from people p2 where p2.name = p.name and p2.type = 'mrr');

The above is usually reasonably efficient. But window functions can also be used:

select p.*
from (select p.*,
             row_number() over (partition by p.name order by charindex(p.type, 'mrr,admin')
                               ) as seqnum
      from people p
     ) p
where seqnum = 1;

The charindex() is just a convenient shorthand for putting values in order. You can also use a case expression, but that is much longer to type.

Upvotes: 1

Related Questions