SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table my_data (
  id integer, 
  name varchar(20)
  );

insert into my_data (id, name) values 
(1, 'john'), 
(2, 'doe'), 
(5, 'rose'), 
(11, 'michael'), 
(15, 'pedro');
5 rows affected

select id, name, 
 row_number() over (order by id) as rn, 
 row_number() over (order by id) + 1 as rn_plus_1
from my_data


id
name
rn
rn_plus_1


1
john
1
2

2
doe
2
3

5
rose
3
4

11
michael
4
5

15
pedro
5
6


with row_data as (
 select id, name, 
  row_number() over (order by id) as rn
 from my_data
  )
select b.id, b.name
from row_data a
join row_data b 
  on a.rn + 1 = b.rn
where a.name in ('john','rose')


id
name


2
doe

11
michael