Cacher is the code snippet organizer for pro developers

;with cte as
(
select name, employeetype, department,
row_number() over(	partition by name,department 
					order by case	when employeetype = '1' then 0
									when employeetype = '2' then 1 
									else 2 end ) As rownumber
from tablename
)

select * from cte;