Hello,
I would like to keep only the required row(s) for the NumberValue.
If NumberValue has one row only, i.e. mpID = -1 --> then keep this row
If NumberValue has more than one row then do NOT keep the row with mpID = -1. Keep the remaining for that NumberValue
I placed a sample code with results here for you.
Hope it helps
create table #tblMain(NumberValue varchar(10), FirstName varchar(10), mpID varchar(10))
insert into #tblMain values ('0006', 'James', '-1')
insert into #tblMain values ('0002', 'Fred', '-1')
insert into #tblMain values ('0002', 'Fred', '0x44')
insert into #tblMain values ('0009', 'Sid', '-1')
insert into #tblMain values ('0009', 'Sid', '0x32')
insert into #tblMain values ('0009', 'Sid', '0x12')
insert into #tblMain values ('0017', 'Roy', '-1')
select * from #tblMain
drop table #tblMain
/
Actual Result to show as follows:
NumberValue FirstName mpID
'0006' 'James' '-1'
'0002' 'Fred' '0x44'
'0009' 'Sid' '0x32'
'0009' 'Sid' '0x12'
'0017' 'Roy' '-1'
/