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
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
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
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
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
be done.

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

Regards,
Benjamin Kalytta

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.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
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