Re: Finding nearest numeric value

On Wed, Aug 17, 2005 at 17:35:37 +0200,
Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
> 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 ;-)

You probably can just add parenthesis. I think that the second ORDER BY
and LIMIT may be being applied to the UNION results which would be a
problem. Putting the second subquery in parens will take care of this if
that is the problem.

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