MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `employee_gb`
(name varchar(50), salary float, dept varchar(50));

INSERT INTO `employee_gb` VALUES('a',1,'dept3'); 
INSERT INTO `employee_gb` VALUES('b',2,'dept4'); 
INSERT INTO `employee_gb` VALUES('c',3,'dept1'); 
INSERT INTO `employee_gb` VALUES('d',4,'dept2'); 
INSERT INTO `employee_gb` VALUES('e',5,'dept2'); 
INSERT INTO `employee_gb` VALUES('g',5,'dept2'); 
INSERT INTO `employee_gb` VALUES('f',6,'dept1'); 
SELECT * FROM employee_gb WHERE (salary,dept) IN(SELECT MAX(salary),dept FROM employee_gb GROUP BY dept)


name
salary
dept


a
1
dept3

b
2
dept4

e
5
dept2

g
5
dept2

f
6
dept1


SELECT t1.* FROM employee_gb t1
INNER JOIN (SELECT MAX(salary) maxsalary,dept FROM employee_gb GROUP BY dept) t2
ON t1.salary = t2.maxsalary AND t1.dept = t2.dept


name
salary
dept


a
1
dept3

b
2
dept4

e
5
dept2

g
5
dept2

f
6
dept1