Apply MIN() OVER only on percentage of the rows

Question

I have the following query:

SELECT *
FROM empno,
     ename,
     deptno,
     sal,
     job,
     MIN(sal) OVER (PARTITION BY deptno, job) AS min_sal_by_dept_and_job
FROM emp;

With this result: Query result

I want the column min_sal_by_dept_and_job to exclude X percent of the sal column. Let's say 50% so in this case the first two rows with sal 1250 and 1250 will be excluded and min_sal_by_dept_and_job will no longer be 1250 but 1500 because it is the min from 1500 and 1600. Simply said, I want the min() to be applied only on certain percentage of the rows.

Answer 1

According to your explanations above you can try this one:

SELECT e.empno, ename, deptno, sal, job, 
       MIN(SAL) OVER (PARTITION BY deptno, job) AS min_sal_by_dept_and_job 
 FROM (SELECT empno, ename, deptno, sal, job,
              count(*) over () as cnt_rows,
              row_number() OVER (PARTITION BY deptno, job ORDER BY sal DESC) AS ROW_num
         FROM emp) e 
WHERE ROW_NUM <=0.5*cnt_rows
GROUP BY empno, ename, deptno, sal, job;