>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, acrossI 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?

>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 ...

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