[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