How to Compare Two Rows in the Same Table in Oracle

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

Assuming that you have a product_prices table that stores the 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 change, 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 the 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.

nxt.id = prv.id + 1Code language: SQL (Structured Query Language) (sql)

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