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

## 6 replies

Last post Dec 07, 2012 01:58 PM by march11

Contributor

2981 Points

1350 Posts

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

I have a MS SQL 2008 R2 DB table with 5 columns of data. colB and colC have non sequencial values like so

colB             colC

12.54         -87.36
41.25          68.56
55.98         -82.45
40.12          78.52

colA is a primary key, colD has a name.

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.

Thank you for any help and direction.

Member

59 Points

20 Posts

### Re: T-SQL query to locate next and previous record of a value.

if you are looking for the closest records after decimal point then

use select top 1 colB from table where convert(int,colb) between 40 and (40+9) order by colB asc

select top 1 (colB-40.81) from table where convert(int,colb) between 40 and (40+9) order by colB asc

if you want multiple records use top 2 or 3 or...n with order by asc or desc

Thx,
Sheik.

All-Star

23373 Points

2490 Posts

Microsoft

### Re: T-SQL query to locate next and previous record of a value.

Hi,

In order to resolve your issue, you can try to do as follows:

```SELECT  [columnA]
,[columnB],[columnB]-48.15 as columnE
FROM [testdatabase].[dbo].[Table_2]
order by abs([columnB]-48.15) asc```

In addition, ABS (Transact-SQL) is a mathematical function that returns the absolute (positive) value of the specified numeric expression. For details, please refer to the link below:

http://msdn.microsoft.com/en-us/library/ms189800.aspx

The ORDER BY keyword is used to sort the result-set. It sorts the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword. For details, refer to:http://www.w3schools.com/sql/sql_orderby.asp

Best wishes,

Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store

Contributor

6779 Points

1058 Posts

### Re: T-SQL query to locate next and previous record of a value.

Check, if you are looking for this

```declare @tab table(
colB numeric(10,2), colC numeric(10,2)
)
insert into @tab values
(12.54,-87.36), (41.25, 68.56), (55.98,-82.45), (40.12, 78.52)

declare @colB numeric(10,2), @colC numeric(10,2)
set @colB = 40.81
set @colC = 75.56

-- if want to check the closest value on the basis of both cols
select top 1 *
from @tab
order by abs(@colB - colB) + ABS(@colC - colC)

-- if want to check closest value in colB first then in colC
select top 1 *
from @tab
order by abs(@colB - colB), ABS(@colC - colC)```

Sandeep Mittal | My Blog - IT Developer Zone

Contributor

2981 Points

1350 Posts

### Re: 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.

All-Star

23373 Points

2490 Posts

Microsoft

### Re: T-SQL query to locate next and previous record of a value.

Hi,

#### march11

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

For your issue, you can modify the code sandeepmittal11 provided and use  CASE expression in T-SQL as follows:

```declare @tab table(
colB numeric(10,2), colC numeric(10,2)
)
insert into @tab values
(12.54,-87.36), (41.25, 68.56), (55.98,-82.45), (40.12, 78.52)

declare @colB numeric(10,2), @colC numeric(10,2)
set @colB = 40.81
set @colC = 75.56

select *,ABS(@colB - colB),ABS(@colC - colC)
from @tab
order by
case when
ABS(@colB - colB)>ABS(@colC - colC)
then ABS(@colC - colC)
else ABS(@colB - colB)
end```

http://www.codeproject.com/Articles/39103/A-Simple-Use-of-SQL-CASE-Expression

http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/

Best wishes,

Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store

Contributor

2981 Points

1350 Posts

### Re: T-SQL query to locate next and previous record of a value.

Hello all,

The solution listed in the previous posts will not work. The correct logic to process this data is provided below.....

```@lat numeric (9,6),
@lon numeric (9,6)

AS

select top 1 *,
((2 * 3960 *
ATN2(
SQRT(
),
SQRT(1-(
))
)
)) AS distance  FROM gpsCoords ORDER BY distance;```

This code was found at the below link.....

http://sgowtham.net/blog/2009/08/08/mysql-finding-locations-nearest-to-a-given-pair-of-gps-coordinates/

Thanks for the feedback. This code is SQL and can be called from C# or VB.net from an ASPX page.

• Edited by march11 on Dec 07, 2012 01:58 PM
• Marked as answer by march11 on Dec 07, 2012 01:58 PM