question

arkiboys avatar image
0 Votes"
arkiboys asked Viorel-1 answered

keep only the required records

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'
/

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Check one of solutions:

 select NumberValue, FirstName, mpID from #tblMain
 except
 select NumberValue, FirstName, '-1' from #tblMain where mpID <> '-1'


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.