I have an integer column, and I want to search a value that could not
be there.
Example column values: 114,54,223,67,187,991
Example search value: 100

How to find row 100 (if exists) or the most approximated value 114?

My actual solution is:
SELECT * FROM table WHERE column < search ORDER BY column DESC LIMIT
0,1
SELECT * FROM table WHERE column > search ORDER BY column DESC LIMIT
0,1

But my doubt is: Is there any function to do this in MySQL?

On 2010-07-04 16:45, Rafael Patro wrote:
Here's one way to do it:

create table tt ( x int not null primary key ) engine = innodb;
insert into tt (x) values (114),(54),(223),(67),(187),(991);

select tt.x
from tt
join (
select min(abs(x-100)) as y
from tt
) as t2
on abs(tt.x-100) = t2.y;

+-----+

/Lennart