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