Prev: Keeping rows with the minimum difference between a column and a givenparameter
Next: Keeping rows with the minimum difference between a column and a given parameter
From: Plamen Ratchev on 30 Apr 2010 10:55
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
GROUP BY RouterId, DeviceId) AS B
ON B.DeviceId = A.DeviceId
AND B.RouterId = A.RouterId
AND ABS(90 - A.Metric) = min_metric;
From: Benjamin Kalytta on 1 May 2010 13:13
Hello Mr. Sommarskog,
thanks for your reply.
> 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.
Yes, but this wouldn't be a problem in my case, but you are right.
> 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.
OK that explains everything :)
> Looking at your query it seems to me that you need the DeviceTable
> in two places:
> SELECT LT1.RouterId, LT1.DeviceId, LT1.Metric
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
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
SELECT NULL AS DeviceId, 0x2[...]c AS MacAddress, 90 AS Metric
) 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
From: Benjamin Kalytta on 1 May 2010 20:45
> 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. :-)
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
If my project is finished you can test the smart device application,
since it will become open source.
From: Plamen Ratchev on 1 May 2010 22:29
Benjamin Kalytta wrote:
> 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).
It will be better to insert all MAC addresses to a permanent table and then use it in the query to join.