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. Theid
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 + 1
Code language: SQL (Structured Query Language) (sql)
Now, you should know how to compare two rows in the same table in Oracle.