I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list.
Unfortunately, the gap from the given timestamp to the first value timestamp for one foreign key may be many times this gap for another FK. I've had a go doing something like this, but it only gives me one row, rather than one row per fk.
SELECT * FROM data_item WHERE fk_fc_id IN (35246,35247) AND di_timestamp > '2013-11-01 00:00:00' ORDER BY di_timestamp ASC LIMIT 1;
It's like I need to take the limit on a per FK basis, but can't work out how to do that.