How do I get records before and after given one?

user_139698 0
Viewed 24029

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.

tsql sql-server sql
7 Answers

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
user_81773550
Related