Re: A VERY DIFFICULT QUERY

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 3 Aug 1999 08:25:42 GMT
Message-ID: <7o6926$ro3$3@news.seed.net.tw>

nooruddin <nooruddin76_at_telebot.net> wrote in message news:7mkql9$ag6$1_at_news.vsnl.net.in...
> Hi!
> I want a sql query for the following result:
> From a table containing salaries (say emp table)
> I want the salary which is closest to the average of all the
> salaries
> (it seems easy at first look, but I assure you its not the case)
> NOTE:The query should be an sql statement and not a pl/sql script.

This is what you want, regardless there are one or more closest salaries.

SQLWKS> select e.empno, e.sal, a.avg_sal, abs(e.sal-a.avg_sal)
     2>   from emp e, (select avg(sal) avg_sal from emp) a
     3>   where abs(e.sal-a.avg_sal)=(select min(abs(sal-a.avg_sal)) from emp);

EMPNO      SAL        AVG_SAL    ABS(E.SAL-
---------- ---------- ---------- ----------
      7782       2450 2073.21429 376.785714

1 row selected. Received on Tue Aug 03 1999 - 03:25:42 CDT