Find nearest value

[bevan07]

Hello and thanks for taking the time to help.

I have two tables ('lastvalue' and 'percentile'). These tables
contain among others fields as below
-lastvalue-

site location lastval
1 1 2.41
1 2 6.89
2 1 12.59


-percentile-

site location percentile value
1 1 25 1.01
1 1 50 2.52
1 2 25 6.50
1 2 50 8.42
2 1 25 12.70
2 1 50 20.68

I would like a query that finds the 'lastval' (from the lastvalue
table) compares it to 'value' (from the percentile table) and returns
the 'percentile'. For example using the data above (the real data has
0.5 percentiles so the values are close to each other.
-result-

site location percent
1 1 50
1 2 25
2 1 25

Again thanks,

 

[Guest]

Interesting question!

SELECT Percentile.site,
Percentile.Location,
Percentile.percentile,
Percentile.value,
Lastvalue.lastval,
Abs([value]-[lastval]) AS Smallest
FROM Percentile INNER JOIN Lastvalue
ON (Percentile.Location = Lastvalue.location) AND (Percentile.site =
Lastvalue.site)
WHERE (((Abs([value]-[lastval])) In
(SELECT Min(Abs([value]-[lastval])) AS Smallest
FROM Percentile INNER JOIN Lastvalue
ON (Percentile.Location = Lastvalue.location)
AND (Percentile.site = Lastvalue.site)
GROUP BY Percentile.site, Percentile.Location)))
ORDER BY Percentile.site, Percentile.Location,
Abs([value]-[lastval]);

 

[bevan07]

Jerry,

Thank you very much for your reply! By some sort of crazy magic it
works.

 

[Guest]

I'd watch it carefully. I'm not 100% sure that the logic works in the case of
ties or some other circumstances.

 

[bevan07]

Thanks,

I have aready found a case where it does strange stuff. Well to
explain there are some strange values in the database and it has
highlighted it. Such as a case where you have only one sample (in a
time series). In this case all the percentiles (from 0 to 100 in 0.5
so n =2) are the same value. In this case (a tie as you mentioned) it
returns all of them. For me this is not a biggie because i am
filering to return datasets of a cerain length and so there shouldn't
be any other ties.

Thanks again