Re: getting the nearest number from a column

From: <hasta_l3_at_hotmail.com>
Date: 11 Apr 2007 02:27:54 -0700
Message-ID: <1176283674.583168.126630@b75g2000hsg.googlegroups.com>

On 11 avr, 09:04, hasta..._at_hotmail.com wrote:
> On 10 avr, 22:56, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net>
> > While the row_number() and rank() tricks will work for the MIN function, they
> > will not work for the max() function. To do that, you will have
> > to order by desc.
>

Hmmm... I may be wrong, but I have a feeling that the semantic you are after is actually provided by FIRST_VALUE and LAST_VALUE (ignoring the windowing issue).

Contrast :

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

  2                    first_value(sal) over (order by abs(sal-1400)
rows between unbounded preceding and unbounded following) as cls
  3                  from emp)

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

ENAME EMPNO DEPTNO SAL ---------- ---------- ---------- ---------- TURNER 7844 30 1500 with :

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

  2                    min(sal) over (order by abs(sal-1400) rows
between unbounded preceding and unbounded following) as cls
  3                  from emp)

  4 select ename,empno,deptno,sal from close   5 where sal=cls
  6 /

ENAME EMPNO DEPTNO SAL ---------- ---------- ---------- ---------- SMITH 7369 20 800

• Raoul

Received on Wed Apr 11 2007 - 04:27:54 CDT