I have the following table structure:

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

开发者_开发问答

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

---

;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')

---

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

---

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)

---

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

---

;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=next.rownum 
WHERE curr.message = 'Error'

---

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"
    )

---

Get fixed number of rows before & after target

Using UNION for a simple, high performance query (I found selected answer WITH query above to be extremely slow)

Here is a high performance alternative to the WITH top selected answer, when you know an ID or specific identifier for a given record, and you want to select a fixed number of records BEFORE and AFTER that record. Requires a number field for ID, or something like date that can be sorted ascending / descending.

Example: You want to select the 10 records before and after a specific error was recorded, you know the error ID, and can sort by date or ID.

The following query gets (inclusive) the 1 result above, the identified record itself, and the 1 record below. After the UNION, the results are sorted again in descending order.

SELECT q.*
FROM(
    SELECT TOP 2
        id, content
    FROM
        the_table
    WHERE 
        id >= [ID]
    ORDER BY id ASC
    UNION
    SELECT TOP 1
        id, content
    FROM
        the_table
    WHERE 
        id < [ID]
    ORDER BY id DESC
) q
ORDER BY q.id DESC