On Apr 9, 8:06 am, hasta..._at_hotmail.com wrote:
> 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
Ranks. Much appreciated.
Received on Tue Apr 10 2007 - 01:41:24 CDT
