Re: Finding nearest numeric value

On 8/17/05 10:01 AM, "Poul Møller Hansen" <freebsd(at)pbnet(dot)dk> wrote:

>
>> To find the nearest value in number_column to some CONSTANT (where you
>> replace constant with a number), try:
>>
>> select *,(number_column - CONSTANT)^2 as d from tablename order by d limit
>> 1;
>>
>> Does that do it for you?
>>
>> Sean
>>
>>
> It does ideed, not that I understood how, but I will find out.
> Thank you very much.

Just a word (or several) of explanation, then....

To compute the distance between two points on a line, you can compute the
absolute value of the difference (4-2 is the same distance as 2-4, while the
latter is negative) or you can square the difference (just to make it
positive). You could use absolute value in the above query if you like--I
don't know which is faster, but they will give the same result.

As for the query structure, you can select calculations of columns as well
as the columns themselves. The "as d" part just gives the calculation a
nice name to use in the rest of the query and in the resulting output.

Sean