Keeping rows with the minimum difference between a column and a givenparameter
in [Using MS SQL Server]
|
Prev: Yellowfin 5.0 Webinar
Next: Keeping rows with the minimum difference between a column and a givenparameter
From: Benjamin Kalytta on 30 Apr 2010 10:32 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 |