How to get previous and next row's value effeciently in SQL server

sql, sql-server, sql-server-2008, t-sql

Solution

Try this:

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 like FIRST_VALUE and LAST_VALUE, unfortunately in sql 2008 these functions are missing.

Problem

Say I have these rows, InstrumentID ``` 547 698 708 ``` InstrumentID is not autogenerated column. Say if I pass the parameter in procedure as 698, I should get previous value as 547 and next value as 708. How do I do this efficiently in SQL? I have this procedure but it is not efficient (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 ```

Original source