SQL Tips: LAG and LEAD Analytic Functions

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.

LAG and LEAD functions

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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)