Select record with nearest value

[allanhicks]

I have a table with fields FA & FD where FA is a key field and FD is a date. I want to know how to select, via sql, one record for each available key where the date is closest to but not more than a specified date.

eg if the table looked like:
FA, FD
1, 01/01/2000
1, 30/03/2001
1, 21/09/2001
2, 23/03/2001
2, 31/10/2001
3, 29/09/2001

and I asked for the records that were closest to 30/06/2001 I want to get back the following records:

FA, FD
1, 30/03/2001
2, 23/03/2001

Hope someone can help.

 

[Guest_imported]

Try the following code:

SELECT FA, MAX(FB)
FROM your_table
WHERE FB <= max_date
GROUP BY FA

Remark: when no occurence of FB for a specific value x of FA is lower than max_date, no record is selected for the specific value x of FA...