hello. a table contains one int column like this:

5
19
22
60
74
80
81
109
120


and so on. notice that these numbers are incrementing, but not with a fixed amount each time.

now, i need to locate the record that a particular number is larger than it, for example, when i query with 40, it returns 22 (because it's the lower number just before my 40).
example2: when quering with 70, it returns 60.


so now i need a select query to achieve this. please support and any help is appreciated.

well, it wasn't a particular mysql question so far. it was just a sql language question. so, i got the answer alone, just two queries, one to select all the lower values , which is a sub query, and the other gets the max of them, and this is the main query. like this:

SELECT max(num) from (select num from my_table where num <= 90 )


with access, oarcle or sql server it will run NO PROBLEM!!!

now we turn to mysql specifications, looks like there is no way of doing that thing. two hours searching with google, and all results were from people asking , and thoses who replied them were all pitying them, crying for them, and not sure of their answers.

i also noticed that my post is the only question post. all the other topics here are advices and new ideas. sorry guys. but you really put me down

would work just as well as the query you posted. Perhpas even better, because it doesn't use a sub-query.

I don't see why this wouldn't work with MySQL. Are you having problems executing it in any way? Are you getting an error message, and if so, what is it?

look. select max(num) from my_table where num <= 90 works ok, yes i know that i don't need a sub-query to get the max value, and i don't just need that max value, i need the field value next to it. they are two columns (num and column2) and sorry i didn't mention that. I just wanted to give an examble of using sub-queries.

i meant: select column2 from my_table where num=( select max(num) from my_table where num <= 90 )
anyway i got a solution for this, i run the first query
select max(num) from my_table where num <= 90
and store the result in an outer variable. then query again with this variable.
thanks much