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

• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

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

  Nov 28, 2012 06:58 PM|LINK

  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.

   

   

   

   

   

• • Reply
  

  sh_asp.net

  Member

  59 Points

  20 Posts

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

  Nov 28, 2012 07:31 PM|LINK

  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.
• • Reply
  

  Catherine Sh...

  All-Star

  23373 Points

  2490 Posts

  Microsoft

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

  Dec 04, 2012 06:38 AM|LINK

  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
  
• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

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

  Dec 04, 2012 08:17 AM|LINK

  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
  
  
• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

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

  Dec 04, 2012 04:02 PM|LINK

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

   

   

• • Reply
  

  Catherine Sh...

  All-Star

  23373 Points

  2490 Posts

  Microsoft

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

  Dec 05, 2012 01:39 AM|LINK

  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

  For details about how to use it, please check the links below:

  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
  
  • Edited by Catherine Shan - MSFT on Dec 07, 2012 12:50 AM
  • Edited by march11 on Dec 07, 2012 01:54 PM
• • Reply
  

  march11

  Contributor

  2981 Points

  1350 Posts

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

  Dec 07, 2012 01:58 PM|LINK

  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(
              POWER(SIN((RADIANS(@lat - INTPTLAT))/2), 2) +
              COS(RADIANS(INTPTLAT)) *
              COS(RADIANS(@lat )) *
              POWER(SIN((RADIANS(@lon - INTPTLONG))/2), 2)
            ),
            SQRT(1-(
              POWER(SIN((RADIANS(@lat - INTPTLAT))/2), 2) +
              COS(RADIANS(INTPTLAT)) *
              COS(RADIANS(@lat )) *
              POWER(SIN((RADIANS(@lon - INTPTLONG))/2), 2)
            ))
          )
        )) 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