How to get next row with id-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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