Hello,

I had no luck with building an sql query to eliminate all rows except
that with MIN(column - @parameter)

Example Table:

RouterId DeviceId Metric Other
------------------------------------------------------
1 1 100 blabla1
1 2 90 blabla2
1 1 50 blabla3
2 1 100 ...
2 1 90 ...
2 1 10 ...

My intention is to compare Metric with a given value like 90 for
example. As we can see, there are rows with equal DeviceId and RouterId.
All that lines (except one) should be removed where DeviceId and
RouterId matches. Only that line with minimum difference between Metric
and given parameter (90) should not be removed.

If we look at RouterId = 1 and DeviceId = 1 we get following rows:

RouterId DeviceId Metric Other
------------------------------------------------------
1 1 100 blabla1
1 1 50 blabla3

So if we compare Metric column with our paramneter, we get following

1. ABS(90 - 100) = 10
2. ABS(90-50) = 40

We want to keep that row with the lowest difference value ... in our
case column 1.

If a general query is not possible, RouterId can be assumed to be known.

SELECT * ... WHERE RouterId = @p1 ... ABS(Metric - @p2) ...

I hope someone can help with with that query.

Some hint: I'm using MS SQL Compact Edition.

Regards,
Benjamin Kalytta