By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE service(
id INT,
position varchar(10),
experience int,
salary int );
insert into service values
(1,'top',90,1500),
(2,'bottom',100,1500),
(3,'top',90,750),
(4,'left',90,1000),
(5,'right',100,1300),
(6,'top',90,1500),
(7,'left',80,2000),
(8,'top',80,1000),
(9,'bottom',100,2000),
(10,'left',100,2000);
Records: 10 Duplicates: 0 Warnings: 0
SELECT s.position,sum(case when max_experience is null then 0 else 1
end ) as max_count
FROM service s
LEFT JOIN (select max(experience) as max_experience
from service
) as s1 ON s.experience = s1.max_experience
group by s.position
order by max_count desc ;
| position | max_count |
|---|---|
| bottom | 2 |
| left | 1 |
| right | 1 |
| top | 0 |