How to find a specific rows + n in SQL Server table
Solution 1:
This can be done via LEAD analytic function as follows:
Query with a two-column result:
With CTE As (
Select [message], Lead([message], 2) Over (Order by id) As message_1
From Tbl
Where [source]='a'
)
Select [message], message_1
From CTE
Where [message] Like '%text1%'
Result:
| message | message_1 |
|---|---|
| text1 | text3 |
| text1 | text5 |
| text1 | text7 |
Query with a single column result:
With CTE As (
Select [message], Lead([message], 2) Over (Order by id) As message_1
From Tbl
Where source='a'
)
Select V.[message]
From CTE Cross Apply (VALUES([message]), (message_1)) As V([message])
Where CTE.[message] Like '%text1%'
Result:
| message |
|---|
| text1 |
| text3 |
| text1 |
| text5 |
| text1 |
| text7 |