Oracle 18c

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

CREATE TABLE employees (id, job_id, salary) AS
SELECT LEVEL, 'AD_PRES', LEVEL * 100 FROM DUAL CONNECT BY LEVEL <= 10
UNION ALL
SELECT 10 + LEVEL, 'AD_PRES', LEVEL * 200 FROM DUAL CONNECT BY LEVEL <= 5
15 rows affected

SELECT * FROM employees;


ID
JOB_ID
SALARY


1
AD_PRES
100

2
AD_PRES
200

3
AD_PRES
300

4
AD_PRES
400

5
AD_PRES
500

6
AD_PRES
600

7
AD_PRES
700

8
AD_PRES
800

9
AD_PRES
900

10
AD_PRES
1000

11
AD_PRES
200

12
AD_PRES
400

13
AD_PRES
600

14
AD_PRES
800

15
AD_PRES
1000


select salary
from   employees
where  job_id = 'AD_PRES'
and    rownum <= 5
order by salary desc;


SALARY


500

400

300

200

100


SELECT salary
FROM   (
  SELECT salary
  FROM   employees
  WHERE  job_id = 'AD_PRES'
  ORDER BY salary DESC
)
WHERE  rownum <= 5;


SALARY


1000

1000

900

800

800


select *
from   employees
where  salary in (
  SELECT salary
  FROM   (
    SELECT salary
    FROM   employees
    WHERE  job_id = 'AD_PRES'
    ORDER BY salary DESC
  )
  WHERE  rownum <= 5
  -- No ORDER BY clause
)


ID
JOB_ID
SALARY


10
AD_PRES
1000

15
AD_PRES
1000

9
AD_PRES
900

8
AD_PRES
800

14
AD_PRES
800


select *
from   employees
where  salary in (
  SELECT salary
  FROM   employees
  WHERE  job_id = 'AD_PRES'
  ORDER BY salary DESC
  FETCH FIRST 5 ROWS ONLY
)


ID
JOB_ID
SALARY


10
AD_PRES
1000

15
AD_PRES
1000

9
AD_PRES
900

8
AD_PRES
800

14
AD_PRES
800

ID	JOB_ID	SALARY
1	AD_PRES	100
2	AD_PRES	200
3	AD_PRES	300
4	AD_PRES	400
5	AD_PRES	500
6	AD_PRES	600
7	AD_PRES	700
8	AD_PRES	800
9	AD_PRES	900
10	AD_PRES	1000
11	AD_PRES	200
12	AD_PRES	400
13	AD_PRES	600
14	AD_PRES	800
15	AD_PRES	1000