How do I write this MySQL query?

Question

I have a table with a column of numbers that are consecutive but not concurrent. So 91 may be followed by 92 or it may be followed by 96, for example) with intervening numbers not in the column.

I want to search on this column for a particular number and then return the entire row if it is found. If the number is not found I want to return the row with the lower number.

So using the example above if I search for 95, which won't be found, I want to return the row with the number 91 in it.

I presume this must be possible but I just can't work out how to do it.

Answer 1

You can use ORDER BY with LIMIT 1 for this:

SELECT t.* -- the columns you want
FROM table_name AS t
WHERE t.column_name <= 95 -- your parameter
ORDER BY column_name DESC
LIMIT 1 ;

• Colin: DOH. Why couldn't I sort that out. Thanks very much it works perfectly.