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

The query is not very difficult to write. The problem is using the Compact Edition which has very limited T-SQL
capabilities. For example, using ROW_NUMBER can help to write a query, but that is not available in CE.

Here is a query that works in SQL CE:

SELECT A.RouterId, A.DeviceId, A.Metric
FROM Foo AS A
JOIN (SELECT RouterId, DeviceId, MIN(ABS(90 - Metric)) AS min_metric
FROM Foo
GROUP BY RouterId, DeviceId) AS B
ON B.DeviceId = A.DeviceId
AND B.RouterId = A.RouterId
AND ABS(90 - A.Metric) = min_metric;

--
Plamen Ratchev
http://www.SQLStudio.com

This should work, no need for UNION. You can use IN instead of ORs, C.MacAddress IN (...).

--
Plamen Ratchev
http://www.SQLStudio.com

Hello again,

unfortunately it doesn't work as expected. SQL CE says, it can't find
column C.Metric


So I think it's time to find another solution. May be I could call that
query for each DeviceId, but SQL CE on my smart device is really slow as
it is.

Benjamin Kalytta

Benjamin Kalytta (bkausbk (AT) web (DOT) de) writes:
No, that query would not work on real SQL Server either.

Let's go back to Plamen's query:

SELECT A.RouterId, A.DeviceId, A.Metric
FROM Foo AS A
JOIN (SELECT RouterId, DeviceId, MIN(ABS(90 - Metric)) AS min_metric
FROM Foo
GROUP BY RouterId, DeviceId) AS B
ON B.DeviceId = A.DeviceId
AND B.RouterId = A.RouterId
AND ABS(90 - A.Metric) = min_metric;

The thing in parenthesis is a *derived table*. Logically, it is a
temp table within the table, but it is not materialised, and the
actual computation order by be different, at least on SQL Server.

The derived table produces the (RouterId, DeviceId) that is closest to
the metric, and then we join back to the whole table to get further
data. Note, by the way, that the query can return more than one row for
the same (RouterId, DeviceID), for instance if you have one tuple with
Metric = 95 and one with Metric = 105.

And important property of a derived table is that it is blind for
tables outside it, why you cannot refer to DeviceTable it. Keep in
mind, that logically the derived table is computed first.

Looking at your query it seems to me that you need the DeviceTable
in two places:

SELECT LT1.RouterId, LT1.DeviceId, LT1.Metric
FROM LinkTable AS LT1
JOIN DeviceTable AS DT1 ON DT1.Id = LT1.DeviceId
AND DT1.MacAddress IN (0x1234567890ab, ...)
JOIN (SELECT LT2.RouterId, LT2.DeviceId,
MIN(ABS(DT2.Metric - LT2.Metric)) AS min_metric
FROM LinkTable LT2
JOIN DeviceTable AS DT2 ON DT2.Id = LT2.DeviceId
AND DT2.MacAddress IN (0x1234567890ab, ...)
GROUP BY LT2.RouterId, LT2.DeviceId) AS LT2
ON LT1.DeviceId = LT2.DeviceId
AND LT1.RouterId = LT2.RouterId
AND ABS(DT1.Metric - LT1.Metric) = LT2.min_metric;


Disclaimer: I have never worked with Compact Edition, and I can't
vouch for that Compact will be able ro run this query.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hello Mr. Sommarskog,

thanks for your reply.

Yes, but this wouldn't be a problem in my case, but you are right.

OK that explains everything

OK, but now the Question is (if this would work in Compact Edition),
would it make sense to make such a complex query? May be it is better to
put some logic outside of the query into the main application?

My real database is even more complex, but I omitted to post tons of
lines of code

What, if there are not only MAC Address parameters, but also Metric
parameters. In my real table there is no metric. Both are provided
parameters:

@p1 @p2
MAC METRIC
0x1234567809ab 100
0x234567809abc 90
.... ...

I tried it with that temporary table, but as you said this is not possible:

SELECT B.DeviceId, A.MacAddress, A.Metric FROM
(
(
SELECT NULL AS DeviceId, 0x1[...]b AS MacAddress, 100 AS Metric
UNION
SELECT NULL AS DeviceId, 0x2[...]c AS MacAddress, 90 AS Metric
UNION
...
) AS A LEFT OUTER JOIN DeviceTable B ON (A.MacAddress = B.MacAddress)
) AS C

OK, but I think, it is better to call that query for each of the MAC
addresses on my list instead to create a real huge query (if this would
be possible).

Regards,
Benjamin Kalytta

Benjamin Kalytta (bkausbk (AT) web (DOT) de) writes:
As I don't know your application, and nor do I have any knowledge of
SQL Server Compact Edition, I cannot give you answer.

But had you been working in "real" SQL Server, my answer would have been
"probably not". (If you think that is a complex query, you should see some
of those I write at work. :-)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

OK, I believe you No I didn't work in "real" SQL Servers so far, only
some study projects with relative simple tables.

As this is also a student project I'll try my best to understand more
complex SQL queries. I don't really like SQL Nevertheless work had to
be done.

If my project is finished you can test the smart device application,
since it will become open source.

Regards,
Benjamin Kalytta

Benjamin Kalytta wrote:

It will be better to insert all MAC addresses to a permanent table and then use it in the query to join.

--
Plamen Ratchev
http://www.SQLStudio.com