Re: getting the nearest number from a column

From: <hasta_l3_at_hotmail.com>
Date: 11 Apr 2007 00:04:45 -0700
Message-ID: <1176275085.869899.226850@y5g2000hsa.googlegroups.com>

On 10 avr, 22:56, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net> wrote:
> On Tue, 10 Apr 2007 12:34:40 -0700, hasta_l3 wrote:
> > Mladen, I agree that one may be surprised by the behavior of MIN, and I
> > agree that the documentation could be improved, to say the least.
>
> > However, I do not agree that there is a documentation bug stricto sensu,
> > for
> > the reference of MIN explicitly requests to look at "Analytic Functions"
> > for
> > semantic, which in turns requests to look at the database warehouse
> > guide.
>
> Raul, I must say it again: that is only my opinion. 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.
>

I'm afraid I dont understand you fully here, Mladen...

One could compute the salary(ies) farthest away from a target with :

    select distinct sal from
    (

        select sal, rank() over (order by abs(sal - 2900) desc) rnk
        from emp

    )
    where rnk <= 1;

Would you have an example illustrating the problem you had in mind ?

Cheers

• Raoul

Received on Wed Apr 11 2007 - 02:04:45 CDT