By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE service(
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
position VARCHAR2(10) NOT NULL,
experience NUMBER NOT NULL,
salary NUMBER NOT NULL ,
PRIMARY KEY(id) );
INSERT INTO service (id, position, experience, salary)
WITH p AS (
SELECT 1, 'top', 90,1500 FROM dual UNION ALL
SELECT 2, 'bottom', 100,1500 FROM dual UNION ALL
SELECT 3, 'top', 90,750 FROM dual UNION ALL
SELECT 4, 'left', 90,1000 FROM dual UNION ALL
SELECT 5, 'right', 100,1300 FROM dual UNION ALL
SELECT 6, 'top', 90,1500 FROM dual UNION ALL
SELECT 7, 'left', 80,2000 FROM dual UNION ALL
SELECT 8, 'top', 80,1000 FROM dual UNION ALL
SELECT 9, 'bottom', 100,1000 FROM dual UNION ALL
SELECT 10, 'left', 100,1000 FROM dual
)
SELECT * FROM p;
10 rows affected
select * from service;
| ID | POSITION | EXPERIENCE | SALARY |
|---|---|---|---|
| 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 | 1000 |
| 10 | left | 100 | 1000 |
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
) s1 ON s.experience = s1.max_experience
group by s.position
order by max_count desc ;
| POSITION | MAX_COUNT |
|---|---|
| bottom | 2 |
| right | 1 |
| left | 1 |
| top | 0 |
select max(experience=100) as max_experience
from service ;
ORA-00907: missing right parenthesis