select t.dept_no,t.rank from employee e, ( SELECT (@rowNO := @rowNo+1) rank, dept_no FROM (SELECT dept_no FROM employee group BY dept_no ) dept_no, (SELECT @rowNO :=0) row ) t where e.dept_no = t.dept_no