CODEWITHSUNDEEP
sql-server-2008t-sql
htm11h
htm11h

Reputation: 1779

T-SQL query to locate next and previous record of a value

I am looking to check for the closest set of coordinates based on both coordinates, against any set of coordinates in the table.

I have a table with 5 columns of data. colB and colC have non sequential values.

colB             colC

12.54         -87.36
41.25          68.56
55.98         -82.45
40.12          78.52

colA is the primary key

I need to pass in two values to a query, like 40.81 for colB value and 75.56 for colC value.

Then determine the closest record between the difference of the four values. In this example, the difference between rec 2 and 4 for colB would be checked against the passed in value 40.81. Hence, 41.25 - 40.81 = .44 and 40.81 - 40.12 = .69 So in this instance, record 2 is closer and I would need to return all of the fields for record 2.

If someone could help get me close to solving this T-SQL query, I think I can complete the next part of the requirement which repeats the process for the colC value.

Upvotes: 0

Views: 322

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24480

I'd do it like this - but I get result 4 as the closest:

declare @example table 
(
    colA bigint not null identity(1,1) primary key clustered
    , colB numeric(18,9)
    , colC numeric(18,9)
)

insert @example select 12.54, -87.36
insert @example select 41.25,  68.56
insert @example select 55.98, -82.45
insert @example select 40.12,  78.52

declare @B numeric(18,9) =  40.81 
, @C numeric(18,9) = 75.56

select top 1 *
from @example
order by power(@B - colB, 2) + power(@C - colC, 2)

Alternative The above gives you the record with the smallest difference in B and C. If you want the smallest difference in B or C use the below:

select top 1 *
from @example
order by 
    case 
        when abs(@B - colB) < abs(@C - colC) 
            then abs(@B - colB) 
        else 
            abs(@C - colC) 
    end

Upvotes: 1

Simon Kingston
Simon Kingston

Reputation: 495

I'm assuming a datatype of float for your columns. What you're looking for is the lowest absolute value of the difference between your passed in value and the colB value. Here's my simple solution that may not be the most performant:

DECLARE @value float = 40.81;

SELECT TOP 1 *
FROM MyTable m
ORDER BY ABS(colB-@value);

Upvotes: 0

Related Questions