How to efficiently use the previous and next row value in SQL Server

Question

Say I have these lines,

Instrumentmentid

547 698 708 

InstrumentID is not an auto-generated column.

Say, if I pass the parameter in the procedure as 698, I should get the previous value as 547, and the next value - 708. How to do this efficiently in SQL?

I have this procedure, but it is not effective (and not correct).

 Alter PROCEDURE GetNextAndPreviousInsturmentID ( @InstrumentID varchar(14), @PreviousInstrumentID varchar(14) OUT, @NextInstrumentID varchar(14) OUT ) AS BEGIN Declare @RowNum int = 0 Select @RowNum = ROW_NUMBER() Over (Order by Cast(@InstrumentID as decimal(18))) From Documents Where InstrumentID = @InstrumentID ;With normal As ( Select ROW_NUMBER() Over (Order by Cast(@InstrumentID as decimal(18))) as RowNum, Cast(InstrumentID as decimal(18)) as InstrumentID From Documents ) Select @PreviousInstrumentID = InstrumentID From normal Where RowNum = @RowNum - 1 Select @NextInstrumentID = InstrumentID From normal Where RowNum = @RowNum + 1 END GO 

Answer 1

Try the following:

 Alter PROCEDURE GetNextAndPreviousInsturmentID ( @InstrumentID varchar(14), @PreviousInstrumentID varchar(14) OUT, @NextInstrumentID varchar(14) OUT ) AS BEGIN Declare @Ids TABLE(Id varchar(14)) ;With normal As ( --Numerate our rows Select ROW_NUMBER() Over (Order by Cast(Documents.InstrumentID as decimal(18)) as RowNumber, Documents.InstrumentID From Documents ) --Insert three rows from our table with our id and previos/next id INSERT INTO @Ids(Id) SELECT TOP(3) normal.InstrumentID FROM normal WHERE RowNumber >= ( SELECT RowNumber - 1 FROM normal WHERE normal.InstrumentID = @InstrumentID ) ORDER BY normal.RowNumber --select next and previos ids SELECT @PreviousInstrumentID = Min(CAST(Id as decimal(18))), @NextInstrumentID = MAX(CAST(Id as decimal(18))) FROM @Ids END GO 

In MS SQL 2012, we have new window functions such as FIRST_VALUE and LAST_VALUE, unfortunately, in sql 2008 these functions are not available.

Answer 2

Here is a simpler solution, but it is more efficient

 SELECT P.PreviousID, N.NextID FROM (SELECT MAX(D.InstrumentID) PreviousID FROM Documents D WHERE InstrumentID < @InstrumentID) P CROSS JOIN (SELECT MIN(D.InstrumentID) NextID FROM Documents D WHERE InstrumentID > @InstrumentID) N 

Answer 3

 WITH CTE AS ( SELECT rownum = ROW_NUMBER() OVER (ORDER BY p.LogDate), p.LogDate FROM DeviceLogs p ) SELECT prev.logdate PreviousValue, CTE.logdate, nex.logdate NextValue FROM CTE LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1 GO 

Answer 4

Hi, I think it will be much more efficient:

Select "Next":

 select top 1 ID from mytable where ID >'698' order by ID asc 

Choose Prev:

 select top 1 ID from mytable where ID <'698' order by ID desc 

Answer 5

select

 LAG(InstrumentID) OVER (ORDER BY InstrumentID) PreviousValue, InstrumentID, LEAD(InstrumentID) OVER (ORDER BY InstrumentID) NextValue from documents