Fetch previous and next row in SQL Server

Question

I need to fetch all the current rows, along with the previous and next rows, in a separate row.

For example:

Table1 has 6 rows:

Column1  Column2  Column3  Column4
----------------------------------
1        abc      1        21
2        def      2        14
3        ghi      2        25
4        jkl      1        34
5        mno      2        23
6        pqr      1        54
7        stu      1        64
8        vw       2        67
9        xyz      2        43
10       yre      1        16
11       tuy      2        53
12       mou      1        30

Table ordered by Column4:

Select previous and next rows from the above table Where Column3 = 1 and then Order By Column4 Desc.

And I have the Column4 = 21, based on this condition I need to select only the Previous and next records.

Expected result:

Column1  Column2  Column3  Column4
----------------------------------
12       mou      1        30
 1       abc      1        21
10       yre      1        16

Answer 1

Try this:

DECLARE @DataSource TABLE
(
    [Column1] INT
   ,[Column2] CHAR(3)
   ,[Column3] INT
   ,[Column4] INT
);

INSERT INTO @DataSource ([Column1], [Column2], [Column3], [Column4])
VALUES (1, 'abc', 1, 21)
      ,(2, 'def', 2, 14)
      ,(3, 'ghi', 2, 25)
      ,(4, 'jkl', 1, 34)
      ,(5, 'mno', 2, 23)
      ,(6, 'pqr', 1, 54)
      ,(7, 'stu', 1, 64)
      ,(8, 'vw', 2, 67)
      ,(9, 'xyz', 2, 43)
      ,(10, 'yre', 1, 16)
      ,(11, 'tuy', 2, 53)
      ,(12, 'mou', 1, 30);

WITH DataSource As
(
    SELECT *
         ,LAG([Column4]) OVER (ORDER BY [Column4] DESC) AS [Next]
         ,LEAD([Column4]) OVER (ORDER BY [Column4] DESC) AS [Prev]
    FROM @DataSource
    WHERE [Column3] = 1
)
SELECT [Column1], [Column2], [Column3], [Column4]
FROM DataSource
WHERE [Column4] = 21
    OR [Next] = 21
    OR [Prev] = 21
ORDER BY [Column4] DESC;