Re: getting the nearest number from a column

On 9 avr, 09:50, "matt" <reflectio..._at_gmail.com> 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

Dear Matt,

A variation on yas answer is

SQL> connect scott/tiger;
Connected.
SQL> select sal from
  2 (
  3 select sal, row_number() over (order by abs(sal - 1400)) rnk   4 from emp
  5 )
  6 where rnk <= 1;

       SAL

      1500

The following variation on the variation selects the two closest salaries when there is a tie :

SQL> select distinct sal from
  2 (
  3 select sal, rank() over (order by abs(sal - 1400)) rnk   4 from emp
  5 )
  6 where rnk <= 1;

       SAL

      1300
      1500

In case you are not (yet :-) familiar with the above kind of queries, the magic search key is "analytic functions"

Cheers

• Raoul