How do I get records before and after given one?

Question

Thanks everyone for the feedback. This is a favorite.

Hi All,

Log Table
Id, Message, Type
1, John Doe
2, Jane Smith
3, Error
4, Jane Smith

Is there a way to get the error record and the surrounding records? Find all Errors and the record before and after them.

Thanks,

rod.

Answer 1

WITH    err AS 
        (
        SELECT  TOP 1 *
        FROM    log
        WHERE   message = 'Error'
        ORDER BY
                id
        ),
        p AS
        (
        SELECT  TOP 1 l.*
        FROM    log
        WHERE   id <
                (
                SELECT  id
                FROM    err
                )
        ORDER BY
                id DESC
        )
SELECT  TOP 3 *
FROM    log
WHERE   id >
        (
        SELECT  id
        FROM    p
        )
ORDER BY
        id

Answer 2

select id, message from tbl where id in (
    select id from tbl where message = "error"
    union
    select id-1 from tbl where message = "error"
    union
    select id+1 from tbl where message = "error"
    )

Answer 3

Adapt this routine to pick out your target.

DECLARE @TargetId  int
SET @TargetId = 3

select *
 from LogTable
 where Id in (--  "before"
              select max(Id)
               from LogTable
               where Id < @TargetId
              --  target
              union all select @TargetId
              --  "after"
              union all select min(Id)
               from LogTable
               where Id > @TargetId)

Answer 4

;WITH numberedlogtable AS
(
SELECT Id,Message, 
ROW_NUMBER() OVER (ORDER BY ID) AS RN
 FROM logtable
)

SELECT Id,Message
FROM numberedlogtable
WHERE RN IN (SELECT RN+i
             FROM numberedlogtable
             CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n
             WHERE Message='Error')

Answer 5

;WITH Logs AS 
(
SELECT ROW_NUMBER() OVER (ORDER BY id), id, message as rownum  FROM LogTable lt
) 
SELECT curr.id, prev.id, next.id 
FROM Logs curr 
LEFT OUTER JOIN Logs prev ON curr.rownum+1=prev.rownum 
RIGHT OUTER JOIN Logs next ON curr.rownum-1=prev.rownum 
WHERE curr.message = 'Error'

Answer 6

select id,messag from 
 (Select (Row_Number() over (order by ID)) as RNO, * from #Temp) as A, 
 (select SubRNO-1 as A, 
  SubRNO as B, 
  SubRNO+1 as C 
  from (Select (Row_Number() over (order by ID)) as SubRNO, * from #Temp) as C
  where messag = 'Error') as B
  where A.RNO = B.A or A.RNO = B.B or A.RNO = B.C