Re: getting the nearest number from a column

From: Mladen Gogala <mgogala.SPAM_ME.NOT_at_verizon.net>
Date: Mon, 09 Apr 2007 12:49:42 GMT
Message-Id: <pan.2007.04.09.12.49.42@verizon.net>

On Mon, 09 Apr 2007 00:50:10 -0700, matt wrote:

> I have a table with a column called 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

Received on Mon Apr 09 2007 - 07:49:42 CDT