select values in range from a table based on a condition

I have a table in sql server and I would like to select rows based on some specific condition.

id name age
1  ABC  33
2  XYZ  45
3  KLM  32
4  HIJ  35
5  PQR  44
6  DEF  55
7  KKK  66
8  XXX  77

I need to search second column name as 'KLM' and if it found that it should return next consecutive range of rows like:

    4  HIJ  35
    5  PQR  44
    6  DEF  55

I am looking for next consecutive 3 rows once name is located

SELECT TOP 3 *
FROM sourcetable
WHERE id > ( SELECT id
             FROM sourcetable
             WHERE name = 'KLM' )
ORDER BY id ASC

or could be the rest of records after that

Remove TOP 3.

An option to get that would be to use something like this code:

Create Table #Testing( Id INT IDENTITY(1,1),
                        Name char(3),
                        age INT)

INSERT INTO #Testing
VALUES('ABC',33),('WYZ',45),('KLM',32),('HIJ',35),('PQR',44),('DEF',55),('KKK',66),('XXX',77)


SELECT *
 FROM #Testing
 WHERE ID BETWEEN ( SELECT Id 
                FROM #Testing
                 WHERE Name = 'KLM'
                ) + 1
            AND 
            ( SELECT Id 
                FROM #Testing
                 WHERE Name = 'KLM'
                ) + 3

That gives you the answer you need

You’ve already accepted Akina’s answer (which definitely gives you the answer you want and is very simple to understand). However, you mentioned in a comment on another answer that you would entertain an answer using a Window Function and case expression. Here is an example (if you’re ‘dying’ to use a window function).

--demo setup
DROP TABLE IF EXISTS Table1;
go
CREATE TABLE Table1
    (id int, name varchar(3), age int)
;

INSERT INTO Table1
    (id, name, age)
VALUES
    (1, 'ABC', 33),
    (2, 'XYZ', 45),
    (3, 'KLM', 32),
    (4, 'HIJ', 35),
    (5, 'PQR', 44),
    (6, 'DEF', 55),
    (7, 'KKK', 66),
    (8, 'XXX', 77);
--solution
;WITH _cte
AS (
    SELECT *
        ,CASE 
            --include me if 1 row before is 'klm'
            WHEN LAG(NAME, 1, 0) OVER (
                    ORDER BY id
                    ) = 'klm'
                THEN 'y'
            --include me if 2 rows before is 'klm'
            WHEN LAG(NAME, 2, 0) OVER (
                    ORDER BY id
                    ) = 'klm'
                THEN 'y'
            --include me if 3 rows before is 'klm'
            WHEN LAG(NAME, 3, 0) OVER (
                    ORDER BY id
                    ) = 'klm'
                THEN 'y'
            --do not include me
            ELSE 'n'
            END AS IncludeRow
    FROM Table1
    )
SELECT *
FROM _cte
WHERE IncludeRow = 'y'

Here’s an example using a variable that I also think is very easy to read.

DECLARE @KLMrow INT =
    (SELECT MAX(ID)
    FROM Table
    WHERE Name = 'KLM')
-- Max is used so even if KLM wasn't unique this doesn't bomb out

SELECT *
FROM Table
WHERE ID BETWEEN (@KLMrow + 1)
  AND (@KLMrow + 3)
ORDER BY ID ASC