How to get next row with id

Question

Lets say I have a table like this;

Id	Name
1	John
2	Doe
5	Rose
11	Michael
15	Pedro

and my select query like this;

Id	Name
1	John
5	Rose

I want to select next rows according to my query which like this;

Id	Name
2	Doe
11	Michael

1 Johns next row is 2 Doe and 5 Roes's next row 11 Michael

What have you tried? What query did you execute to get just `John` and `Rose`? — Jim Rhodes, Oct 19 '22 at 21:03
Not sure I understand - are you running these queries back to back? Or do you want the result to include all four rows? Description is very confusing and I don't understand how to solve the problem. — Stuck at 1337, Oct 19 '22 at 21:35

score 0 · Answer 1 · answered Oct 19 '22 at 21:25

One of many ways to do this:

WITH 
    RowNumbers AS (
        SELECT
            *,
            ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
        FROM
            TableName -- replace with your table name
        )

SELECT
    Id,
    Name
FROM
    RowNumbers
WHERE
    RowNumber IN (
        SELECT 
            RowNumber+1 
        FROM 
            RowNumbers 
        WHERE 
            Name IN ('John', 'Rose')
        )
;

score 0 · Answer 2 · answered Oct 19 '22 at 21:27

You could use a CTE to create row_numbers and then select and join to it.

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');

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

fiddle

How to get next row with id

2 Answers2