How to compare previous row with current in oracle

Home » Articles » Misc » Here

Índice Show

Introduction
Quick Links

The LAG and LEAD analytic functions were introduced in 8.1.6 to give access to multiple rows within a table, without the need for a self-join. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Setup
Introduction
LAG
LEAD
Quick Links

Setup

The examples in this article require the following table.

--DROP TABLE emp PURGE; CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;

Introduction

Both LAG and LEAD functions have the same usage, as shown below. The analytic clause elements are described in more detail here.

LAG { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] ) } OVER ([ query_partition_clause ] order_by_clause) LEAD { ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] ) } OVER ([ query_partition_clause ] order_by_clause)

value_expr - Can be a column or a built-in function, except for other analytic functions.
offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default - The value returned if the offset is outside the scope of the window. The default value is NULL.

Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno, ename, job, sal FROM emp ORDER BY sal; EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7369 SMITH CLERK 800 7900 JAMES CLERK 950 7876 ADAMS CLERK 1100 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7934 MILLER CLERK 1300 7844 TURNER SALESMAN 1500 7499 ALLEN SALESMAN 1600 7782 CLARK MANAGER 2450 7698 BLAKE MANAGER 2850 7566 JONES MANAGER 2975 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7839 KING PRESIDENT 5000 SQL>

LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno, ename, job, sal, LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev, sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff FROM emp; EMPNO ENAME JOB SAL SAL_PREV SAL_DIFF ---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 800 0 800 7900 JAMES CLERK 950 800 150 7876 ADAMS CLERK 1100 950 150 7521 WARD SALESMAN 1250 1100 150 7654 MARTIN SALESMAN 1250 1250 0 7934 MILLER CLERK 1300 1250 50 7844 TURNER SALESMAN 1500 1300 200 7499 ALLEN SALESMAN 1600 1500 100 7782 CLARK MANAGER 2450 1600 850 7698 BLAKE MANAGER 2850 2450 400 7566 JONES MANAGER 2975 2850 125 7788 SCOTT ANALYST 3000 2975 25 7902 FORD ANALYST 3000 3000 0 7839 KING PRESIDENT 5000 3000 2000 SQL>

If the LAG would span a partition boundary, the default value is returned. In the following example we partition by department, so the SAL_PREV column has a default value of "0" for the first row in each department.

SELECT deptno, empno, ename, job, sal, LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_prev FROM emp; DEPTNO EMPNO ENAME JOB SAL SAL_PREV ---------- ---------- ---------- --------- ---------- ---------- 10 7934 MILLER CLERK 1300 0 10 7782 CLARK MANAGER 2450 1300 10 7839 KING PRESIDENT 5000 2450 20 7369 SMITH CLERK 800 0 20 7876 ADAMS CLERK 1100 800 20 7566 JONES MANAGER 2975 1100 20 7788 SCOTT ANALYST 3000 2975 20 7902 FORD ANALYST 3000 3000 30 7900 JAMES CLERK 950 0 30 7654 MARTIN SALESMAN 1250 950 30 7521 WARD SALESMAN 1250 1250 30 7844 TURNER SALESMAN 1500 1250 30 7499 ALLEN SALESMAN 1600 1500 30 7698 BLAKE MANAGER 2850 1600 SQL>

LEAD

The LEAD function is used to return data from rows further down the result set. The following query returns the salary from the next row to calculate the difference between the salary of the current row and the following row.

SELECT empno, ename, job, sal, LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next, LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff FROM emp; EMPNO ENAME JOB SAL SAL_NEXT SAL_DIFF ---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 800 950 150 7900 JAMES CLERK 950 1100 150 7876 ADAMS CLERK 1100 1250 150 7521 WARD SALESMAN 1250 1250 0 7654 MARTIN SALESMAN 1250 1300 50 7934 MILLER CLERK 1300 1500 200 7844 TURNER SALESMAN 1500 1600 100 7499 ALLEN SALESMAN 1600 2450 850 7782 CLARK MANAGER 2450 2850 400 7698 BLAKE MANAGER 2850 2975 125 7566 JONES MANAGER 2975 3000 25 7788 SCOTT ANALYST 3000 3000 0 7902 FORD ANALYST 3000 5000 2000 7839 KING PRESIDENT 5000 0 -5000 SQL>

If the LEAD would span a partition boundary, the default value is returned. In the following example we partition by department, so the SAL_NEXT column has a default value of "0" for the last row in each department.

SELECT deptno, empno, ename, job, sal, LEAD(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_next FROM emp; DEPTNO EMPNO ENAME JOB SAL SAL_NEXT ---------- ---------- ---------- --------- ---------- ---------- 10 7934 MILLER CLERK 1300 2450 10 7782 CLARK MANAGER 2450 5000 10 7839 KING PRESIDENT 5000 0 20 7369 SMITH CLERK 800 1100 20 7876 ADAMS CLERK 1100 2975 20 7566 JONES MANAGER 2975 3000 20 7788 SCOTT ANALYST 3000 3000 20 7902 FORD ANALYST 3000 0 30 7900 JAMES CLERK 950 1250 30 7654 MARTIN SALESMAN 1250 1250 30 7521 WARD SALESMAN 1250 1500 30 7844 TURNER SALESMAN 1500 1600 30 7499 ALLEN SALESMAN 1600 2850 30 7698 BLAKE MANAGER 2850 0 SQL>

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

❇️ OrclQA.Com is a question and answer forum for programmers. ❇️ Here anyone can ask questions and anyone can answer to help others. ❇️ It hardly takes a minute to sign up and it is 100% FREE.

Have an account? Sign In

Summary: in this tutorial, you will learn how to compare two rows in same table in Oracle.

Assuming that you have a product_prices table that stores price history of all products. The following statement creates the product_prices table:

CREATE TABLE product_prices ( id NUMBER generated BY DEFAULT AS identity, product_id NUMBER, valid_from DATE, list_price NUMBER, PRIMARY KEY(id), UNIQUE (product_id, valid_from, list_price) );

Code language: SQL (Structured Query Language) (sql)

The product_prices table has the following columns:

id is an identity column whose values are generated automatically. The id is the primary key of the table.
product_id stores the product id that identifies a product.
valid_from stores the effective date from which the list price is valid.
list_price stores the list price of a product.

The following INSERT statements insert some rows into the product_prices table:

INSERT INTO product_prices(product_id, valid_from, list_price) VALUES(100, DATE '2016-01-01', 700); INSERT INTO product_prices(product_id, valid_from, list_price) VALUES(100, DATE '2016-06-01', 630); INSERT INTO product_prices(product_id, valid_from, list_price) VALUES(100, DATE '2016-08-01', 520); INSERT INTO product_prices(product_id, valid_from, list_price) VALUES(100, DATE '2017-01-01', 420);

Code language: SQL (Structured Query Language) (sql)

As you can see, the price of the product id changes. To find the differences between each subsequent changes, you need to compare two successive rows.

We assume that there is no gap in the id column and the list price with the earlier valid from date is inserted before the list price with later valid from date, the following query compares prices between each change of the product id 100:

SELECT cur.product_id, cur.valid_from, cur.list_price, (cur.list_price - prv.list_price) diff FROM product_prices prv INNER JOIN product_prices cur ON cur.id = prv.id+1 WHERE cur.product_id = 100;

Code language: SQL (Structured Query Language) (sql)

In this query, we used the self-join that joins the product_prices table to itself. The following join predicate allows the current row with the previous row.

Code language: SQL (Structured Query Language) (sql)

Now, you should know how to compare two rows in the same table in Oracle.

Was this tutorial helpful?