MySQL select closest value

Question

I have a table containing shipping rates and maximum weights, eg:

max_weight     shipping_cost
100            1.50
250            3.00
500            5.00
1000           8.50
30000          12.50

I want to be able to get the shipping rate based on the weight of the order, where the weight is less than max_weight in the table. So if the weight is 410, the shipping cost would be 5.00, if the weight is 2000 the shipping is 12.50 and so on.

Using max_weight >= '" . $weight . "' doesn't work as it just returns the first max_weight that's more than the weight, eg a weight of 683 returns 12.50 as the shipping cost.

How do I make sure it gets the correct max_weight?

Answer 1

Just use ORDER BY and LIMIT cleverly.

   WHERE weight < max_weight
ORDER BY max_weight ASC 
   LIMIT 1