Re: getting the nearest number from a column

A quick solution. May not be feasible because of the sort if your table have lots of rows.

SQL> create table t(salary number);

Table created.

SQL> insert into t select trunc(dbms_Random.value(1000,5000)) from dual connect by level<11;

10 rows created.

SQL> select * from t;

    SALARY

      3734
      3484
      3345
      1079
      1311
      1425
      2505
      2289
      2779
      4833

10 rows selected.

SQL> select salary from (select salary from t order by abs(&1-salary))   2 where rownum=1;
Enter value for 1: 1500
old 1: select salary from (select salary from t order by abs(&1- salary))
new 1: select salary from (select salary from t order by abs(1500- salary))

    SALARY

      1425

SQL> r
  1 select salary from (select salary from t order by abs(&1-salary))   2* where rownum=1
Enter value for 1: 3300
old 1: select salary from (select salary from t order by abs(&1- salary))
new 1: select salary from (select salary from t order by abs(3300- salary))

    SALARY

      3345 Received on Mon Apr 09 2007 - 04:08:26 CDT