Real Time Scenarios of ORACLE SQL developement - 2

How to extract the Successor and Predecessor of a student based on marks.

Tables:

drop table student;
create table student(id int,name varchar(10),dept int,marks int);
insert into student values(1,'Vishnu',111,95);

insert into student values(2,'Ashwin',111,26);
insert into student values(3,'Vinay',111,56);
insert into student values(4,'Ajay',222,48);
insert into student values(5,'Teja',222,92);
insert into student values(6,'Venkat',333,28);
insert into student values(7,'Maneesh',333,12);
select * from student;

To extract the successor and predecessor of each student based on the marks ORACLE supports LEAD and LAG functions.

LEAD Function:

Allows to access the following row from the current row with out join.

Syntax:

Lead(column name) over (partition by column name order by column name)

LAG Function:

Allows to access the prior row from the current row with out join.

Syntax:

Lag(column name) over (partition by column name order by column name)

Note: Returns NULL, if no value after or before the current value.

Query:

select id,name,dept,marks,lag(name) OVER (partition by dept ORDER BY marks desc) AS predecessor, lead(name) OVER (partition by dept ORDER BY marks desc) AS successor from student;