delete duplicates with a null value

I have a query that is pretty long - at the end before I do the select I want to delete duplicate serial numbers with no jobtype
how can I do this
Example data
type|SN|qty|job type
____________________________
TN        87       1       
____________________________
TN        87       1      surefire
____________________________
TN        88       1       
____________________________
TN        88       1      surefire
____________________________
LVL 6
r3nderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
did you try something to the effect
delete from table 
where isNULL(Type,'') = ''

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
By 'delete' do you mean not show them in your query, or actually delete rows?

Probably a more efficient way to pull this off, but borrowing from my article at SQL Server Delete Duplicate Rows Solutions, the 'DELETE #1' section

Copy-paste the below T-SQL into your SSMS, execute to verify it does what you want, then modify to fit your needs
CREATE TABLE #tmp (type char(2), SN int, qty int, jobtype varchar(10))

INSERT INTO #tmp (type, SN, qty, jobtype) 
VALUES 
	('TN', 87, 1, NULL), ('TN', 87, 1, 'surefire'), ('TN', 87, 1, NULL),
	('TN', 88, 1, NULL), ('TN', 88, 1, 'surefire'),
	('TN', 89, 1, NULL)

;with a as (
   SELECT type, SN, qty, COUNT(qty) as row_count
   FROM #tmp
   GROUP BY type, SN, qty
   HAVING COUNT(qty) > 1
)
DELETE 
FROM #tmp
FROM #tmp
	JOIN a ON a.type = #tmp.type AND a.SN = #tmp.SN AND a.qty = #tmp.qty 
WHERE #tmp.jobtype IS NULL

SELECT * FROM #tmp

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as we are speaking about "duplicates", I would rather do this:
delete t1
from table  t1
where isNULL(t1.Type,'') = ''
 and exists ( select null from table t2 where t2.type = t1.type and T2.SN = T1.SN and T2.qty = T1.qty
   and isNULL(t2.Type,'') <> ''
  )

Open in new window


of course, it depends on your definition of "dupliates"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
r3nderAuthor Commented:
That would work but it would delete data that was not a duplicate
revised Example data:
TN      51      1       
____________________________
TN      66      1       
____________________________
TN      70      1       
____________________________
TN      87      1       
____________________________
TN      87      1      surefire
____________________________
TN      88      1       
____________________________
TN      88      1      surefire
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< edited first comment after it was originally submitted, pls hit refresh >
0
Mike EghtebasDatabase and Application DeveloperCommented:
This shows records to be deleted. verify if this is what you want to be deleted?
Select *  
from (Select rank() over(Partition by [type],SN,qty order by jobtype desc) as rnk
,[type],SN,qty,jobtype
from table1) As t
Where rnk=2

Open in new window


job type is changed to jobtype

if so then run: 
;With d 
as
(
Select *  
from (Select rank() over(Partition by [type],SN,qty order by jobtype desc) as rn
,[type],SN,qty,jobtype
from table1) As t
Where rn=2 
)
delete from d;

Open in new window



Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r3nderAuthor Commented:
@Jim
here is the query I used and the output after
TN      47      1      surefire
____________________________
TN      49      1       
____________________________
TN      49      1      surefire
____________________________
TN      51      1       
____________________________
TN      66      1       
____________________________
TN      70      1       
____________________________
TN      87      1       
____________________________
TN      87      1      surefire
____________________________
TN      88      1       
____________________________
TN      88      1      wildfire
____________________________
;with a as (
   SELECT tooltype,jobtype, serialnumber, qty, COUNT(qty) as row_count
   FROM temp_mytools WHERE tooltype = 'TN'
   GROUP BY jobtype, serialnumber, qty,tooltype
   HAVING COUNT(qty) > 1
)
DELETE 
FROM temp_mytools
FROM temp_mytools
	JOIN a ON a.jobtype = temp_mytools.jobtype AND a.serialnumber = temp_mytools.serialnumber AND a.qty = temp_mytools.qty and a.tooltype = temp_mytools.tooltype 
WHERE temp_mytools.jobtype IS NULL

Open in new window

0
r3nderAuthor Commented:
worked great thanks eghtbas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.