Sql->lag() ->read the previous record data.

 Student Table has three columns Student_Name,Total_Marksand year.User has to write a sql Query to display Student_Name,Total_Marks,Prev_yr_mrks for those whose total_marks are greater than or equal to the previous year

i/p:

Student_Name Total_Marks  year

raghul 90    2010

Sanjay 80    2010

Mohan 70    2010

raghul 90    2011

Sanjay 85    2011

Mohan 65    2011

raghul 80    2012

Sanjay 80    2012

Mohan 90    2012

o/p:

Student_Name  Total_Marks year  prev_yr_marks

raghul 90    2010    90

Sanjay 80    2010    80

Mohan 70    2010    65

Create table Emp_details

(

 Student_Name varchar(10),

 Total_Marks int,

 year int

 );

 

insert into emp_details values("raghul",90,2010);

insert into emp_details values("Sanjay",80,2010);

insert into emp_details values("Mohan",70,2010);

insert into emp_details values("raghul",90,2011);

insert into emp_details values("Sanjay",85,2011);

insert into emp_details values("Mohan",65,2011);

insert into emp_details values("raghul",80,2012);

insert into emp_details values("Sanjay",80,2012);

insert into emp_details values("Mohan",90,2012);

select * from emp_details;

lag() ->read the previous record data. 

select Student_Name,total_marks,year, lag(total_marks) over(partition by student_name order by year) as prv_yr_marks from emp_details;

# Student_Name, total_marks, year, prv_yr_marks

'Mohan', '70', '2010', NULL

'Mohan', '65', '2011', '70'

'Mohan', '90', '2012', '65'

'raghul', '90', '2010', NULL

'raghul', '90', '2011', '90'

'raghul', '80', '2012', '90'

'Sanjay', '80', '2010', NULL

'Sanjay', '85', '2011', '80'

'Sanjay', '80', '2012', '85'

select Student_Name,total_marks,year,prv_yr_marks, 

case when total_marks >= prv_yr_marks then 1 else 0 end as flag

from (select Student_Name, total_marks, year, 

lag(total_marks) over(partition by Student_Name order by year) as prv_yr_marks from emp_details) A; 

# Student_Name, total_marks, year, prv_yr_marks, flag

'Mohan', '70', '2010', NULL, '0'

'Mohan', '65', '2011', '70', '0'

'Mohan', '90', '2012', '65', '1'

'raghul', '90', '2010', NULL, '0'

'raghul', '90', '2011', '90', '1'

'raghul', '80', '2012', '90', '0'

'Sanjay', '80', '2010', NULL, '0'

'Sanjay', '85', '2011', '80', '1'

'Sanjay', '80', '2012', '85', '0'

select Student_Name,total_marks,year,prv_yr_marks 

from

(

select Student_Name,total_marks,year,prv_yr_marks, 

case when total_marks >= prv_yr_marks then 1 else 0 end as flag

from

(select Student_Name, total_marks, year, 

lag(total_marks) over(partition by Student_Name order by year) as prv_yr_marks from emp_details

) A

) B 

where flag=1; 

# Student_Name, total_marks, year, prv_yr_marks

'Mohan', '90', '2012', '65'

'raghul', '90', '2011', '90'

'Sanjay', '85', '2011', '80'