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