SQL server 2008 查询，根据其他表中的字段排序？？？

如图，根据表B中的部门编号对表A进行排序，。。如何写sql啊？？

select a.name,b.depart from tableA a left join tableB on a.depart=b.depart order by b.num desc

select a.name,b.depart from tableA a left join tableB b on a.depart=b.depart order by b.num desc

 select a.name,b.depart from tableA a left join tableB b on a.depart=b.depart order by b.num desc

select a.name,b.depart ;
from a,b ;
where a.depart＝b.depart ;
into cursor temp
select ＊ into a from temp