Re: Finding nearest numeric value

The only problem is that you can't use the order by/limit syntax inside
the union queries I guess, cause the query you proposed is giving a
syntax error. I also thought first to do it like this, but it won't
work. If it would, then you could wrap the thing in another query which
orders by the difference and limits to the first one ;-)

Cheers,
Csaba.

On Wed, 2005-08-17 at 17:10, Richard Huxton wrote:
> Poul Møller Hansen wrote:
> > Does anyone know how to find the row with the nearest numeric value, not
> > necessarily an exact match ?
>
> While the other answers all do their job, and in one go too, I'd be
> surprised if you found anything faster than:
>
> SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1
> UNION ALL
> SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1
>
> That gives you (up to) two values to look at, but should use any index
> you have on myval.
>
> You can always sort the results by abs(myval) then if you don't want to
> handle two values in the application layer.