Return all records that has particular record following next to it

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”

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
    

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