Selecting record with closest key value 1

[RobBroekhuis]

I'm looking for the syntax that will find the record whose key is equal to, or as close as possible but smaller than, the value sought. Can it be done in a simple query, or do I have to use a MAX of all that are <= to get this job done?
Thanks in advance,


Rob

 

[r937]

yes, SELECT MAX(key) FROM daTable WHERE key <= value

r937.com | rudy.ca

 

[RobBroekhuis]

OK, thanks. So it's two steps - first find the key value, then select again to get the field values associated with that key?
select * for key=closestkey

Rob

 

[towerbase]

Something like this might meet your requirements:

SELECT [i]fields[/i]
  FROM [i]daTable [/i]
  WHERE [i]key[/i] <= [i]value [/i]
  ORDER BY [i]key[/i] DESC
  LIMIT 1

This sorts the records that have a key less than or equal to value into descending order but then limits the result set to the first record only which is presumably the one you want. Obviously you replace the italicised fields with your own names.

Andrew
Hampshire, UK

 

[RobBroekhuis]

So simple! Thanks much.

Rob