MySQL Group sort row_number () over (partition by)

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: [Email protected]+1,
if (@pdept =a.deptid, @rank: [Email protected]+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;

MySQL Group sort row_number () over (partition by)