Re: getting the nearest number from a column

From: <hasta_l3_at_hotmail.com>
Date: 10 Apr 2007 08:56:06 -0700
Message-ID: <1176220566.397481.142550@w1g2000hsg.googlegroups.com>

On 10 avr, 14:23, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Mon, 09 Apr 2007 23:38:40 -0700, hasta_l3 wrote:
> > I'm afraid I dont understand how the query above fulfills the OP
> > requirements, as it selects many rows for - say - salary 1400.
>
> I consider this to be a bug. The idea of my query is the same
> as the idea of your query, only with a different analytic function.
> That means that "min" doesn't work as advertised and that the "rank"
> function should be used. Thanks for discovering it. I used only one
> value and was satisfied with the result.
>

Would you mean a bug in Oracle (documentation), Mladen ?

Granted, this area is somewhat tricky, but I don't see a bug here, for my understanding is :

• RANK is a ranking function, that is computed for the whole group/partition
• MIN() is a windowing function, that is computed for the current window.

In the following query :

  select ename,empno,deptno,sal,
  min(sal) over (order by abs(sal-1440)) cls   from emp

the window extents from the first to the current row, by virtue of the order_by clause without windowing clause.

The expression min(sal) over ... will therefore compute the smallest salary of a growing set of rows, and will converge to the smallest salary of the whole partition.

In turn, the overall query

  with close as (select ename,empno,deptno,sal,

                        min(sal) over (order by abs(sal-1400)) as cls
                        from emp)

  select ename,empno,deptno,sal from close   where sal=cls;

will therefore select one or more rows, and the result will always include the smallest employee salary.

This behavior is pretty apparent in :

SQL> connect scott/tiger
Connected.
SQL> select ename,empno,deptno,sal,
  2 min(sal) over (order by abs(sal-1440)) cls   3 from emp;

ENAME EMPNO DEPTNO SAL CLS ---------- ---------- ---------- ---------- ----------

TURNER           7844         30       1500       1500
MILLER           7934         10       1300       1300
ALLEN            7499         30       1600       1300
WARD             7521         30       1250       1250
MARTIN           7654         30       1250       1250
ADAMS            7876         20       1100       1100
JAMES            7900         30        950        950
SMITH            7369         20        800        800
CLARK            7782         10       2450        800
BLAKE            7698         30       2850        800
JONES            7566         20       2975        800

ENAME           EMPNO     DEPTNO        SAL        CLS
---------- ---------- ---------- ---------- ----------
SCOTT            7788         20       3000        800
FORD             7902         20       3000        800
KING             7839         10       5000        800

14 rows selected.

Funny stuff :-)

• Raoul

Received on Tue Apr 10 2007 - 10:56:06 CDT