Re: A VERY DIFFICULT QUERY

I don't know if this is the best solution, but the following is what first came up into my mind (cartesian join):

To get everything in order of difference:

select a.sal, b.avg_sal, abs(a.sal - b.avg_sal) diff from emp a, (select avg(sal) avg_sal from emp) b order by diff

And in order to get only the first (or more, if there are two or more salaries with the same difference):

select a.sal, b.avg_sal, abs(a.sal - b.avg_sal) diff from emp a, (select avg(sal) avg_sal from emp) b where abs(a.sal - b.avg_sal) =
  (select min(abs(c.sal - d.avg_sal)) diff

         from emp c, (select avg(sal) avg_sal from emp) d)

Marc

nooruddin wrote in message <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.
>good luck..
>
>
>bye..
>
>
>
Received on Thu Jul 15 1999 - 00:43:24 CDT