Re: A VERY DIFFICULT QUERY

From: <nvcinc_at_ibm.net>
Date: Thu, 15 Jul 1999 00:41:53 -0400
Message-ID: <378D6691.86A430C5@ibm.net>

Hi,

One way is to write a function:

SQLWKS> SELECT AVG(sal) FROM emp;
AVG(SAL)

---

2073.21429
1 row selected.
SQLWKS> SELECT empno, sal FROM emp ORDER BY 2; EMPNO SAL
---------- ----------

      7369        800
      7900        950
      7876       1100
      7521       1250
      7654       1250
      7934       1300
      7844       1500
      7499       1600
      7782       2450
      7698       2850
      7566       2975
      7788       3000
      7902       3000
      7839       5000

14 rows selected.

SQLWKS> CREATE OR REPLACE FUNCTION get_closest RETURN NUMBER IS
     2>   t_avg NUMBER;
     3>   CURSOR get_close_empno IS
     4>     SELECT empno, ABS(sal - t_avg) diff
     5>       FROM emp
     6>       ORDER BY 2;
     7>   t_empno emp.empno%TYPE;
     8>   t_sole_temp NUMBER;
     9> BEGIN
    10>   SELECT AVG(sal)
    11>     INTO t_avg
    12>     FROM emp;
    13>   OPEN get_close_empno;
    14>   FETCH get_close_empno INTO t_empno, t_sole_temp;
    15>   CLOSE get_close_empno;
    16>   RETURN(t_empno);
    17> END get_closest;
    18> /

Statement processed.
SQLWKS> SELECT get_closest FROM emp WHERE get_closest = empno; GET_CLOSES

---

      7782
1 row selected.

nooruddin wrote:

> 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 Wed Jul 14 1999 - 23:41:53 CDT