Re: SELECT question

I won't vouch for the performance of this solution but it should work:

	select var1, ... 
		from tab 
		where abs(1.000 - var1) = (
			select min(abs(1.000 - var1)) from tab);

You may also want to use 'distinct' on the first 'select' if you can have duplicate values in your data and are not interested in finding out about different minimums if they occur.

A more efficent solution could be written in PL/SQL but the exact form would depend more on whether or not duplicate minimums could exist and, if they do, whether you are interested in them.

I hope this is of some help. Perhaps someone else can suggest a more elegant/efficient solution.

David Feldcamp

Parallel Computation Laboratory
Dept. of Computer Science
University of British Columbia

In article <CoB2zF.5ur_at_cdsmail.cdc.com>, Chuck McMacken <chuck_at_pdm4340.arh.cdc.com> wrote:
>I have a SELECT problem I've been unable to resolve and would greatly
>appreciate your help.
>
>The simplified table is described as var1 number(4,3) and contains values
>in the range 9.999 to -9.999. I need to select the value of var1 which is
>closest to 1.000. I'm able to use the ABS and MIN functions to locate the
>row containing target value and the delta, but I haven't been able to
>retrieve the actual content of var1 from the target row.
>
>Given a list for var1 of:
>
> SELECT min(abs(1.000 - var1) from tab;
>
> returns a delta of .013 for the 0.837 entry which is the row I'm seeking
> in the example.
>
>Unfortunately, I haven't been able to find a way to utilize the result of
>this select or sub-select/self-join to retrieve the 0.837 value. Any replies
>can either be e-mailed or posted.
Received on Sat Apr 16 1994 - 02:24:30 CEST