drop table if exists tmp1;
create table tmp1 (empid int ,deptid int ,salary decimal(10,2) );
insert into tmp1 values
(1,10,5500.00),
(2,10,4500.00),
(3,20,1900.00),
(4,20,4800.00),
(5,40,6500.00),
(6,40,14500.00),
(7,40,44500.00),
(8,50,6500.00),
(9,50,7500.00);
select empid,deptid,salary,rank from (
select a.empid,a.deptid,a.salary,@rownum:=@rownum+1 ,
if(@pdept=a.deptid,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=a.deptid
from
(
select empid,deptid,salary from tmp1 order by deptid asc ,salary asc
) a ,
(select @rownum :=0 , @pdept := null ,@rank:=0) b
) result ;