[thelist] Re: SQL - nearest in time

[David Shadovitz]

Thanks for the help.

Eric, this slightly simpler version of what you suggested works:

SELECT * FROM Measurements
WHERE TimeInSeconds = (SELECT min(TimeInSeconds) FROM Measurements WHERE
TimeInSeconds > :InputTime)

Sean, the "TOP" predicate is not standard SQL and is unavailable to this
Oracle user.

-David the laggard

=== 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.