delete duplicates with a null value

did you try something to the effect

delete from table 
where isNULL(Type,'') = ''

Select allOpen in new window

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

Select allOpen in new window

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,'') <> ''
  )

Select allOpen in new window

of course, it depends on your definition of "dupliates"

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

< edited first comment after it was originally submitted, pls hit refresh >

@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

Select allOpen in new window

worked great thanks eghtbas