[thelist] SQL - nearest in time
Means, Eric D
eric.d.means at boeing.com
Wed Apr 24 17:12:01 CDT 2002
> -----Original Message-----
> From: David Shadovitz [mailto:david_shadovitz at xontech.com]
> Sent: Wednesday, April 24, 2002 5:01 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] SQL - nearest in time
>
> I've got a database table containing measurements taken every few
> seconds, non-periodically:
> Measurements {PK ExperimentID, TimeInSeconds, Item1, Item2, Item3}
...
> 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.
One way to select the nearest record before or the nearest record after (but
not both) would be (pseudo-code ahead)
SELECT *
FROM Measurements
WHERE TimeInSeconds = (SELECT min(TimeInSeconds) FROM
(SELECT TimeInSeconds FROM Measurements WHERE TimeInSeconds >
:InputTime))
For the nearest time before change min() to max() and > to <.
If your DB won't allow subselects in the FROM clause, select the times into
a temp table and then select the min or max from there instead.
More information about the thelist
mailing list