LAG and LEAD functions can be used in Oracle PL/SQL and apply on Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i versions.
Let’s understand the difference between LAG and LEAD analytical functions in Oracle.
First create a table called Student and insert some records in the table.
CREATE TABLE STUDENT (
STUDENT_ID NUMBER(4),
STUD_NAME VARCHAR2(20),
DEPT VARCHAR2(3),
SCHOLARSHIP NUMBER(5));
INSERT INTO STUDENT VALUES(2001,'AMIT KUMAR','CS',800);
INSERT INTO STUDENT VALUES(2002,'SUMIT ARORA','CS',950);
INSERT INTO STUDENT VALUES(2003,'ANKITA VERMA','CS',1100);
INSERT INTO STUDENT VALUES(2004,'ROBERT','EC',1250);
INSERT INTO STUDENT VALUES(2005,'SUDHEER MISHRA','EC',1250);
INSERT INTO STUDENT VALUES(2006,'ANANYA KUMAR','EC',1300);
INSERT INTO STUDENT VALUES(2007,'MAYANK VERMA','EC',1500);
INSERT INTO STUDENT VALUES(2008,'SUDESH SHARMA','EC',1600);
INSERT INTO STUDENT VALUES(2009,'RAVI KUMAR','IT',2450);
INSERT INTO STUDENT VALUES(2010,'DINESH AGARWAL','IT',2850);
INSERT INTO STUDENT VALUES(2011,'AMIT AGARWAL','IT',2950);
INSERT INTO STUDENT VALUES(2012,'ANAMIKA SAIN','IT',3000);
INSERT INTO STUDENT VALUES(2013,'DEVENDRA TRIPATHI','IT',3000);
INSERT INTO STUDENT VALUES(2014,'ANIL KUMAR','IT',5000);
COMMIT;
Retrieve data from Student Table:
First retrieve data from student table with order by clause on scholarship column.
SELECT STUDENT_ID,
STUD_NAME,
DEPT,
SCHOLARSHIP
FROM STUDENT
ORDER BY SCHOLARSHIP;
STUDENT_ID STUD_NAME DEPT SCHOLARSHIP
---------- -------------------- -------------------- -----------
2001 AMIT KUMAR CS 800
2002 SUMIT ARORA CS 950
2003 ANKITA VERMA CS 1100
2004 ROBERT EC 1250
2005 SUDHEER MISHRA EC 1250
2006 ANANYA KUMAR EC 1300
2007 MAYANK VERMA EC 1500
2008 SUDESH SHARMA EC 1600
2009 RAVI KUMAR IT 2450
2010 DINESH AGARWAL IT 2850
2011 AMIT AGARWAL IT 2950
2012 ANAMIKA SAIN IT 3000
2013 DEVENDRA TRIPATHI IT 3000
2014 ANIL KUMAR IT 5000
14 rows selected.
SQL>
LAG FUNCTION
Let’s understand the use of LAG function.
LAG function is used to access data from a previous row in the table. It means if you want to calculate the difference between the scholarships of current row and previous row then following query will help you.
ORDER BY SCHOLARSHIP clause is used to order the data by SCHOLARSHIP.
SELECT STUDENT_ID,
STUD_NAME,
DEPT,
SCHOLARSHIP,
LAG(SCHOLARSHIP, 1, 0) OVER (ORDER BY SCHOLARSHIP) AS SCHOLAR_S_PREV,
SCHOLARSHIP - LAG(SCHOLARSHIP, 1, 0) OVER (ORDER BY SCHOLARSHIP) AS SCHOLAR_S_DIFF
FROM STUDENT;
STUDENT_ID STUD_NAME DEP SCHOLARSHIP SCHOLAR_S_PREV SCHOLAR_S_DIFF
---------- -------------------- --- ----------- -------------- --------------
2001 AMIT KUMAR CS 800 0 800
2002 SUMIT ARORA CS 950 800 150
2003 ANKITA VERMA CS 1100 950 150
2004 ROBERT EC 1250 1100 150
2005 SUDHEER MISHRA EC 1250 1250 0
2006 ANANYA KUMAR EC 1300 1250 50
2007 MAYANK VERMA EC 1500 1300 200
2008 SUDESH SHARMA EC 1600 1500 100
2009 RAVI KUMAR IT 2450 1600 850
2010 DINESH AGARWAL IT 2850 2450 400
2011 AMIT AGARWAL IT 2950 2850 100
2012 ANAMIKA SAIN IT 3000 2950 50
2013 DEVENDRA TRIPATHI IT 3000 3000 0
2014 ANIL KUMAR IT 5000 3000 2000
14 rows selected.
SQL>
LEAD FUNCTION
Let’s understand the use of LEAD function.
LEAD function is used to return data from next row in the table. It means if you want to calculate the difference between the scholarships of current row and following row then following query will help you.
SELECT STUDENT_ID,
STUD_NAME,
DEPT,
SCHOLARSHIP,
LEAD(SCHOLARSHIP, 1, 0) OVER (ORDER BY SCHOLARSHIP) AS SCHOLAR_S_NEXT,
LEAD(SCHOLARSHIP, 1, 0) OVER (ORDER BY SCHOLARSHIP)-SCHOLARSHIP AS SCHOLAR_S_DIFF
FROM STUDENT;
STUDENT_ID STUD_NAME DEP SCHOLARSHIP SCHOLAR_S_NEXT SCHOLAR_S_DIFF
---------- -------------------- --- ----------- -------------- --------------
2001 AMIT KUMAR CS 800 950 150
2002 SUMIT ARORA CS 950 1100 150
2003 ANKITA VERMA CS 1100 1250 150
2004 ROBERT EC 1250 1250 0
2005 SUDHEER MISHRA EC 1250 1300 50
2006 ANANYA KUMAR EC 1300 1500 200
2007 MAYANK VERMA EC 1500 1600 100
2008 SUDESH SHARMA EC 1600 2450 850
2009 RAVI KUMAR IT 2450 2850 400
2010 DINESH AGARWAL IT 2850 2950 100
2011 AMIT AGARWAL IT 2950 3000 50
2012 ANAMIKA SAIN IT 3000 3000 0
2013 DEVENDRA TRIPATHI IT 3000 5000 2000
2014 ANIL KUMAR IT 5000 0 -5000
14 rows selected.
SQL>
I hope you understood the difference between LAG and LEAD analytical functions.
