How do I get N records before given one?

Question

How do I get N records before given one?

I have the following table structure:

Id, Message
1, John Doe
2, Jane Smith
3, Error
4, Jane Smith
5, Michael Pirs
7, Gabriel Angelos
8, Error

Is there a way to get the N records before each Error and join all such records? So the expected result for the N =2 will be

1, John Doe
2, Jane Smith
5, Michael Pirs
7, Gabriel Angelos

Fiddle

Answer 1

You need to create a row number column if your Ids do not increment without gaps. Then you can use a simple join to find the previous N. Your previous N could overlap... so you have to add distinct if you do not want duplicates.

declare @N as integer
set @N=2

;with cte_tbl (Id, Message, rownum) AS
(
select *, ROW_NUMBER() over (order by id) as rownum from test
)
select distinct Prev.Id, Prev.Message
from cte_tbl
join cte_tbl Prev
on Prev.rownum between cte_tbl.rownum-@N and cte_tbl.rownum-1
where cte_tbl.Message = 'Error'
    and Prev.Message <> 'Error'
order by Prev.Id

If the one of the previous @N records is an error, the 'error' record will NOT show up. This would have to be modified if you do want those to be included. Just simply remove the line and Prev.Message <> 'Error'.