List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 3 2006 11:01pm
Subject:Re: Finding the closest value
View as plain text  
Lee

/>I think the formula is just right - very clever. But is it possible to 
make
 >sure that col1, col2 and col3 are all from the same record?
/
Different question, different answer I ween  :-)

SET @n = <?>;
SELECT
  id,
  MIN( LEAST( ABS(@n-col1), LEAST( ABS(@n-col2), ABS(@n-cl3) ))) AS min
FROM tbl
GROUP BY id;

PB

-----

Lee Goddard wrote:
> Peter Brawley wrote:
>> />Is there an efficient way to find the closest numerical value, across
>> >three columns? I do not know if there is a way to find it across one. /
>>
>> Not sure about efficiency, but if the criterion number is @n, do you 
>> mean ...
>>
>> SELECT LEAST( ABS(MIN(@n-col1)),
>>               LEAST( ABS(MIN(@n-col2)),
>>                      ABS(MIN(@n-col3))
>>                    )
>>             )
>> FROM ...
> I think the formula is just right - very clever. But is it possible to 
> make sure that col1, col2 and col3 are all from the same record?
>
> Thanks in anticipation
> Lee
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006
Thread
Finding the closest valueLee Goddard31 Jul
  • Re: Finding the closest valuePeter Brawley31 Jul
    • Re: Finding the closest valueLee Goddard3 Aug
      • Re: Finding the closest valuePeter Brawley4 Aug