CODEWITHSUNDEEP
sqloracle-database
jayz
jayz

Reputation: 401

Compare the same column in 2 rows of a same table with each other

I am using a oracle database and I have the following table with data of documents. I can add the same document with different revision. So that the keys would be doc_id and revision. There can be as many as revisions for a given document.

doc_id revision data
A 1 test1
A 2 test2
A 3 test3
B 1 new1
B 2 new2

I want to create a view comparing data column with each revision of the document. The output of the view should be like below.

doc_id compared_revision old_data new_data
A 1-2 test1 test2
A 1-3 test1 test3
A 2-3 test2 test3
B 1-2 new1 new2

Please help me with creating such view. If this is not possible with the given data set, please suggest me a workaround. Thanks in advance.

Upvotes: 0

Views: 664

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

This looks like a simple self-join:

select t1.doc_id, (t1.revision || '-' || t2.revision) as compared_revision, t1.data, t2.data
from t t1 join
     t t2
     on t1.doc_id = t2.doc_id and t1.revision < t2.revision
order by t1.doc_id, compared_revision;

Here is a db<>fiddle.

Upvotes: 1

MT0
MT0

Reputation: 167774

You can do it (without needing to perform a self-join or scan the table twice) using a hierarchical query:

SELECT doc_id,
       PRIOR revision || '-' || revision AS compared_revision,
       PRIOR data AS old_data,
       data AS new_data
FROM   table_name
WHERE  LEVEL = 2
CONNECT BY
       PRIOR doc_id   = doc_id
AND    PRIOR revision < revision

Which, for the sample data:

CREATE TABLE table_name ( doc_id, revision, data ) AS
SELECT 'A', 1, 'test1' FROM DUAL UNION ALL
SELECT 'A', 2, 'test2' FROM DUAL UNION ALL
SELECT 'A', 3, 'test3' FROM DUAL UNION ALL
SELECT 'B', 1, 'new1' FROM DUAL UNION ALL
SELECT 'B', 2, 'new2' FROM DUAL;

Outputs:

DOC_ID | COMPARED_REVISION | OLD_DATA | NEW_DATA
:----- | :---------------- | :------- | :-------
A      | 1-2               | test1    | test2   
A      | 1-3               | test1    | test3   
A      | 2-3               | test2    | test3   
B      | 1-2               | new1     | new2

db<>fiddle here

Upvotes: 2

Related Questions