>What is your defenition of 'nearest' in this case?
>
>Dear Sir,
>
>I told it in my example.

>
>Suppose the data is as
>
>5
>6
>8
>9
>
>If text1 is equal to 7 then I want to get 6.
>I do not want 8


You told what you wanted. Better would be a description of how you determine this.

What if you have
1
2
3
4
9

and text1 is 8? Do you want 4 or 9?????
if text1 matches one of the values in the table do you want that value or the one "closest" one.

Assuming you want the closest value that is less than text1

SELECT MAX(myField) FROM myTable WHERE myField < m.myVar

might do the trick.

It appears from the original display that you might have character values. Bear in mind that "2" is greater than "123." If this isn't the behavior you want you will need to do something (convert to numeric or add leading zeros).