I have few records sorted by Serial Number and I need all records such that a record followed by another record satisfying given condition.
Table student -> CREATE TABLE student (sno int, id int, firstname varchar(255));
Condition: Record should have firstname = “alex” and must be followed by record with firstname = “justin”
x
Input:
sno id firstname
1 1 alex
2 2 justin
3 1 alex
4 3 Seth
5 2 justin
6 1 alex
7 2 justin
8 2 justin
Output:
sno id firstname
1 1 alex
2 2 justin
6 1 alex
7 2 justin
Advertisement
Answer
You can use lead()
/lag()
:
select s.*
from (select s.*,
lag(name) over (order by sno) as prev_name,
lead(name) over (order by sno) as next_name
from student s
) s
where (name = 'alex' and next_name = 'justin') or
(name = 'justin' and prev_name = 'alex')