Prev: Keeping rows with the minimum difference between a column anda given parameter
Next: US-MD-Silver Spring: SQL VMWare TV Network DBA
From: Benjamin Kalytta on 30 Apr 2010 12:00
Hello Mr. Ratchev,

> Here is a query that works in SQL CE:
> [...]

Thank you for that, it really works. I even understand your query, don't
know why I wasn't able to find it by my self.

However my real query will be a bit more complex because there is not
only one metric to compare, but several metrics. This compareable
metrics are stored in another table which will be referenced by their
MAC Address:

JOIN DeviceTable C ON (C.Id = A.DeviceId AND (C.MacAddress =
0x1234567890ab OR C.MacAddress = ... OR ...)) ...

I don't know if it is better to use UNIONS here or to make your query a
bit more complex if that even works:

SELECT A.RouterId, A.DeviceId, A.Metric
FROM LinkTable AS A
JOIN DeviceTable AS C ON (C.Id = A.DeviceId AND (C.MacAddress =
0x1234567890ab OR C.MacAddress = ... OR ...))
JOIN (SELECT RouterId, DeviceId, MIN(ABS(C.Metric - Metric)) AS min_metric
FROM LinkTable
GROUP BY RouterId, DeviceId) AS B
ON B.DeviceId = A.DeviceId
AND B.RouterId = A.RouterId
AND ABS(C.Metric - A.Metric) = min_metric;

But that should work isn't it?

Regards,
Benjamin Kalytta
From: Plamen Ratchev on 30 Apr 2010 12:08
This should work, no need for UNION. You can use IN instead of ORs, C.MacAddress IN (...).

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 1 May 2010 09:29
Benjamin Kalytta (bkausbk(a)web.de) writes:
> unfortunately it doesn't work as expected. SQL CE says, it can't find
> column C.Metric
>
> SELECT A.RouterId, A.DeviceId, A.Metric
> FROM LinkTable AS A
> JOIN DeviceTable AS C ON (C.Id = A.DeviceId AND (C.MacAddress =
> 0x1234567890ab OR C.MacAddress = ... OR ...))
> JOIN (SELECT RouterId, DeviceId, MIN(ABS(C.Metric - Metric)) AS min_metric
> FROM LinkTable
> GROUP BY RouterId, DeviceId) AS B
> ON B.DeviceId = A.DeviceId
> AND B.RouterId = A.RouterId
> AND ABS(C.Metric - A.Metric) = min_metric;

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Erland Sommarskog on 1 May 2010 17:35
Benjamin Kalytta (bkausbk(a)web.de) writes:
> 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?

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Keeping rows with the minimum difference between a column anda given parameter
Next: US-MD-Silver Spring: SQL VMWare TV Network DBA