Oracle SCOTT schema request. Which salary is closest to average?

Question

Using SCOTT Schema . I'm trying to get a man whose salary is close to average.

SELECT sal
      FROM (  SELECT sal
                FROM emp
            ORDER BY ABS ( (SELECT AVG (SAL) FROM EMP) - sal))
     WHERE ROWNUM = 1;

Is it possible to improve the solution above?

Answer 1

Uses a non-standard Oracle function. The data in the subqueries does not have an initial order, but at Oracle you can order it to be able to apply the ROWNUM criteria later. ROWNUM is also Oracle specific.

In addition, there may be several employees with the same salary, where you choose one of them arbitrarily, and do not show them all.

(-) SQL:

select *
from emp
order by abs(sal - avg(sal) over())
fetch first row with ties;

Oracle 12c.

( Oracle 9i, 10i 11g) :

select empno, ename, sal
from
(
  select empno, ename, sal, rank() over (order by diff) as rnk
  from
  (
    select emp.*, abs(sal - avg(sal) over()) as diff
    from emp
  ) evaluated
) ranked
where rnk = 1;

Answer 2

with av as (select avg(sal) avgsal from scott.emp)
select emp.*, abs(emp.sal-av.avgsal) dist 
  from scott.emp, av 
  order by dist;

, , , . - :

select * from (
  select emp.*, abs(avg(emp.sal) over () - sal) diff 
    from scott.emp order by diff) 
where rownum = 1;

Answer 3

Return employee data back to subquery:

select empno, sal, SalDif
from 
  (
    select empno, sal, abs(sal - (select avg(sal) from emp)) as SalDif
    from emp
    order by SalDif
  )
where rownum = 1;

or as CTE:

with CTE as
  (
    select empno, sal, abs(sal - (select avg(sal) from emp)) as SalDif
    from emp
    order by SalDif
  )
select empno, sal, SalDif
from CTE
where rownum = 1;