[thelist] SQL - nearest in time

Sean ethanol at mathlab.sunysb.edu
Wed Apr 24 18:42:01 CDT 2002


Howdy,

Could you do something along the lines of:


select	TOP 1 Item1, Item2, Item3, abs(input.TimeinSeconds -
Measurements.TimeinSeconds) as TimeDifference
from		Measurements
order by	TimeDifference ASC


Of course the issue with this logic is working through all the records
and calculating TimeDifference for each to return a single record.


HTH,


Sean G.


-----Original Message-----

I've got a database table containing measurements taken every few
seconds, non-periodically: Measurements {PK ExperimentID, TimeInSeconds,
Item1, Item2, Item3}

I'd appreciate it if someone could help me with the SQL to retrieve the
record containing the measurements taken nearest to an input time.  If
there were an exact match I'd simply do:

select Item1, Item2, Item3 from Measurements where ExperimentID = 1 and
TimeInSeconds = <input time>

But the difficulty is that here won't be an exact match between
TimeInSeconds and the input time.  I'll settle for the nearest record,
or the nearest record before the input time, or nearest record after the
input time.

Thanks.
-David




More information about the thelist mailing list