On Wed, Aug 17, 2005 at 17:35:37 +0200, Csaba Nagy <nagy@xxxxxxxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
