-- Oracle 19c: APPROX_SUM, APPROX_RANK function


SELECT
  loc_id
 ,job_cd
 ,APPROX_SUM(val)       approx_sum_val
 ,APPROX_RANK(
         PARTITION BY loc_id
         ORDER BY APPROX_SUM(val)
         DESC)          approx_rank_
FROM
 (SELECT
    level                 idx
   ,CASE MOD(level,3)
      WHEN 0 THEN  'CEO'
      WHEN 1 THEN  'MGR'
      ELSE 'EMP'
    END                   job_cd
   ,MOD(level,5)+1000     loc_id
   ,10000+level*5         trans_id 
   ,level*3.14159         val
  FROM DUAL
  CONNECT BY level < 7
 )
GROUP BY
  loc_id
 ,job_cd
HAVING
  APPROX_RANK(PARTITION BY loc_id
         ORDER BY  APPROX_SUM(val) DESC) < 5   
;