[thelist] SQL - nearest in time

David Shadovitz david_shadovitz at xontech.com
Wed Apr 24 16:57:01 CDT 2002


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

<tip type="Editors and color-coding" author="David Shadovitz">

I'm using Visual SlickEdit (VSE) for PL/SQL coding.  VSE recognizes that
files with the extension "sql" contain SQL code, and applies a nice
color coding.  However, VSE does not recognize the common convention of
using the "sps" extension for package specs and "spb" for package
bodies.  Goodbye, color coding.
You can easily tell VSE to treat .sps and .spb files the same was as
.sql files.  Go to Tool > Configuration > File Extension Setup... and
click on New.  For the Extension, type "sps", and for Refers-to, select
"sql".  (Repeat for "spb".)

OK, this is a pretty narrow tip.  But in general, know that good editors
are flexible and can accommodate your style.  Don't let the editor's
defaults limit you.

</tip>




More information about the thelist mailing list