Home > Net >  How to get next row with id
How to get next row with id

Time:10-20

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

  • Related