inexact integer query

I posted the following on alt.databases but got no reply at all so I am
hoping that the rules can be bent just a wee bit.  If there's anyone
here who can field this, please do.

Thanks.

---

I have a situation where I want to find either an exact match of an
integer timestamp field, or if not found, the closest lower or higher value.

Naturally, there's an issue where the query might return something way
off the mark, but that's fine, I can deal with it once I see the result.

There's the "LIKE" clause for strings, but I can't seem to find anything
equivalent for an integer query.  (I recally from years ago that
CA-Clipper has a "set exact" setting that when turned OFF will do this
nicely).

Is there either an equivalent in MySQL, or a method of doing this that
anyone can suggest?

Thanks for considering my question.

Hi Mark
I think you might be better of setting a window around the value, eg + or -
5 or whatever.
eg
SELECT THEFIELD FROM THETABLE
WHERE
(THEFIELD = :THEVALUE) OR
(THEFIELD BETWEEN (:THEVALUE-:THEOFFSET) AND (:THEVALUE+:THEOFFSET))

You then need to set the paramaters,
ie THEVALUE = the value you are trying to compare
THEOFFSET = the window offset factor.

THEOFFSET could be a global variable making it easier to change if required,
or even a database setting.
HTH
Dominic

"Mark Richards" <nospam@massmicro.com> wrote in message
news:425f2034$0$5889$9a6e19ea@news.newshosting.com...
> I posted the following on alt.databases but got no reply at all so I am
> hoping that the rules can be bent just a wee bit.  If there's anyone
> here who can field this, please do.
>
> Thanks.
>
> ---
>
> I have a situation where I want to find either an exact match of an
> integer timestamp field, or if not found, the closest lower or higher
value.
>
> Naturally, there's an issue where the query might return something way
> off the mark, but that's fine, I can deal with it once I see the result.
>
> There's the "LIKE" clause for strings, but I can't seem to find anything
> equivalent for an integer query.  (I recally from years ago that
> CA-Clipper has a "set exact" setting that when turned OFF will do this
> nicely).
>
> Is there either an equivalent in MySQL, or a method of doing this that
> anyone can suggest?
>
> Thanks for considering my question.

Dominic,

Hello and thanks.  I had considered doing a query using a range of
values, but the issue is that it will return (I assume here) the first
value that it encounters in the range - which may not be the closest one
to the actual value I want. (also I don't know what to set the range to,
as the data is not consistent enough).  My assumption is quite
simplistic as I bet MySQL does some pretty fancy dancing around as it
ss within a range.

eg:

database records
field="nval"
0
3
9
13
14
22
34

inexact search .."FOR nval>9 and nval <14"

returns: 11 - when I was searching for 10.  It could have returned a
closer value, 9, but it wasn't within the limit.


Note: my data s is against unix timestamps.  The above example is
just to simplify things.

"Mark Richards" <nospam@massmicro.com> wrote in message
news:425f314d$0$5897$9a6e19ea@news.newshosting.com...
> Dominic,
>
> Hello and thanks.  I had considered doing a query using a range of values,
> but the issue is that it will return (I assume here) the first value that
> it encounters in the range - which may not be the closest one to the
> actual value I want. (also I don't know what to set the range to, as the
> data is not consistent enough).  My assumption is quite simplistic as I
> bet MySQL does some pretty fancy dancing around as it ss within a
> range.
>
> eg:
>
>  database records
>  field="nval"
>   0
>   3
>   9
>   13
>   14
>   22
>   34
>
> inexact search .."FOR nval>9 and nval <14"
>
> returns: 11 - when I was searching for 10.  It could have returned a
> closer value, 9, but it wasn't within the limit.
>


That's relatively easy. Something like SELECT nval, ABS($my_stamp - nval) as
Difference ORDER BY Difference
Which simply subrtacts one from the other and which ever timestamp is the
closest gets shortest "distance" between the two timestamps. ABS just
converts negative distances to positive. Looking for 10 you'd get

ABS(10 -  0) = 10
ABS(10 -  3) = 7
ABS(10 -  9) = 1
ABS(10 -  13) = 3
ABS(10 -  14) = 4
ABS(10 -  22) = 12
ABS(10 -  34) = 24

When you "order by Difference", you'll get first what resulted 1 -> 9. It
also works for exact match 10 -> 10-10 = 0, which would be returned first.


--
Welcome to Usenet! Please leave tolerance, understanding
and intelligence at the door. They aren't welcome here.
eternal piste erection miuku gmail piste com

Mark Richards wrote:
> I have a situation where I want to find either an exact match of an
> integer timestamp field, or if not found, the closest lower or higher
> value.
>
Kimmo Laine wrote:
> That's relatively easy. Something like SELECT nval, ABS($my_stamp - nval) 
as
> Difference ORDER BY Difference

You could refine Kimmo's solution by appending a LIMIT 1.
But there is a more interesting aspect here.  The query must go through
the entire table.  If this is a once in a while type of query on a small dat
abase,
no problem.  However, if you are looking at a large number of entries, this 
is
not an efficient way to go.

Specifically, you would expect the nval field to be indexed.  But the ABS()
negates the value of the index since all the values must be calculated to de
termine
the result (though mySQL could, in theory, conceivably optimize it).
Something like the following should retain the index's utility
and hence be significantly more efficient for sizeable databases:

SELECT nval WHERE nval>=$my_stamp ORDER BY nval, ASC LIMIT 1

and also === UNION

SELECT nval WHERE nval<=$my_stamp ORDER BY nval, DESC LIMIT 1

will get you at most two entries which you can decide between.


Csaba Gabor from Vienna