SQL SERVER – How to Access the Previous Row and Next Row value

Using CTE

WITH CTE AS(

    SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID),

    p.FirstName FROM Person.Person p

)

SELECT

prev.FirstName PreviousValue,

    CTE.FirstName,

    nex.FirstName NextValue

FROM CTE

LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1

LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

GO







Using Lag and Lead




SELECT

LAG(p.FirstName) OVER(ORDER BY p.BusinessEntityID) PreviousValue,

    p.FirstName,

    LEAD(p.FirstName) OVER(ORDER BY p.BusinessEntityID) NextValue

FROM Person.Person p

GO