From: Peter Brawley
Date: August 3 2006 11:01pm
Subject: Re: Finding the closest value
List-Archive: http://lists.mysql.com/mysql/200598
Message-Id: <44D28032.8050902@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-44D280325CA2======="
--=======AVGMAIL-44D280325CA2=======
Content-Type: multipart/alternative; boundary=------------090900090906010506070404
--------------090900090906010506070404
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
--------------090900090906010506070404
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
--------------090900090906010506070404--
--=======AVGMAIL-44D280325CA2=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-44D280325CA2=======--