try this:

select column1 from table1

except

select column1 from table2

this will give all the values in column1 in table1 that are not in cloumn1 of table2.

--Happy coding

Seems like you are using Rank() instead of Dense_Rank()

i don't think you need to use the count function.

select t.id, SUM(type) from @t t

group by id

having SUM(type) = 0