Re: getting the nearest number from a column

From: <hasta_l3_at_hotmail.com>
Date: 9 Apr 2007 23:38:40 -0700
Message-ID: <1176187120.140961.40540@b75g2000hsg.googlegroups.com>

On 9 avr, 14:49, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Mon, 09 Apr 2007 00:50:10 -0700, matt wrote:
> > I have a table with acolumncalled salary. There are other columns as
> > well but for the most part irrelevant to my problem. I want to be able
> > to retrieve the closest salary value based on what a user inputs.
>
> > SAL
> > ===
> > 4000
> > 3200
> > 2900
> > 2000
> > 800
>
> > user input: 1450
>
> > I would like to get back "2000" as the closest value. I don't seem
> > to see a useful operator such as "TOP" in mssql.
>
> > Any help would be appreciated.
> > Thanks
>
> 1 with close as (select ename,empno,deptno,sal,
> 2 min(sal) over (order by abs(sal-100)) as cls
> 3 from emp)
> 4 select ename,empno,deptno,sal from close
> 5* where sal=cls
> SQL> /
>
> ENAME EMPNO DEPTNO SAL
> ---------- ---------- ---------- ----------
> SMITH 7369 20 800
>
> The role of use input is played by the number 100. Of course,
> this query is terribly inefficient and will always require a
> full table scan as there are no filtering conditions of any kind
> on the emp table. The SCOTT.EMP table used here is reasonable so
> the query didn't take too long to coplete.
>
> --http://www.mladen-gogala.com

Dear Mladen,

I'm afraid I dont understand how the query above fulfills the OP requirements, as it selects many rows for - say - salary 1400.

SQL> connect scott/tiger
Connected.
SQL> with close as (select ename,empno,deptno,sal,

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

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

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

MILLER           7934         10       1300
WARD             7521         30       1250
MARTIN           7654         30       1250
ADAMS            7876         20       1100
JAMES            7900         30        950
SMITH            7369         20        800

6 rows selected.

Cheers

• Raoul

Received on Tue Apr 10 2007 - 01:38:40 CDT