CODEWITHSUNDEEP
sqloracle-databasesorting
anoop ramachandran
anoop ramachandran

Reputation: 19

Order Data based on previous row data

I have a query in Oracle SQL. My query gives three columns: old_data ,new_data and transaction_date. I want to sort this data primarily based on increasing transaction_date and secondly in such a way that new_data of previous row equals old_data of next row. Both new_data and old_date are number fields that can decrease or increase.

If I sort just by transaction_date, some data has the same exact date and time and hence the order will not be accurate as I need new_data of previous row to match old_data of current row. I also cannot use a hierarchical query alone to meet the second sorting condition since transaction_date sorting is the primary sorting condition.

Can anyone suggest a solution?

A sample output will need to look like below: output_sample

Thanks in advance

Upvotes: 0

Views: 103

Answers (1)

MT0
MT0

Reputation: 168671

You could use a hierarchical query and connect by equal dates as well as the relationship between old- and new-data:

SELECT transaction_date,
       new_data,
       old_data
FROM   table_name
START WITH old_data IS NULL -- You need to define how to pick the first row
CONNECT BY
       PRIOR transaction_date = transaction_date
AND    PRIOR new_data = old_date
ORDER SIBLINGS BY
       transaction_date

Which, for the sample data:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CREATE TABLE table_name ( transaction_date, new_data, old_data ) AS
SELECT DATE '2022-01-01', 1, NULL FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 2, 1 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 3, 2 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 3, NULL FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 1, 3 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 2, 1 FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 4, NULL FROM DUAL;

Outputs:

TRANSACTION_DATE NEW_DATA OLD_DATA
2022-01-01 00:00:00 1 null
2022-01-01 00:00:00 2 1
2022-01-01 00:00:00 3 2
2022-01-02 00:00:00 3 null
2022-01-02 00:00:00 1 3
2022-01-02 00:00:00 2 1
2022-01-03 00:00:00 4 null

fiddle

Update

Given the updated sample data:

CREATE TABLE table_name ( transaction_date, old_data, new_data ) AS
SELECT DATE '2021-12-20'+INTERVAL '11:25' HOUR TO MINUTE,   0,       115.09903 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:25' HOUR TO MINUTE, 115.09903, 115.13233 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:25' HOUR TO MINUTE, 115.13233, 115.16490 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:25' HOUR TO MINUTE, 115.16490, 115.19678 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:35' HOUR TO MINUTE, 115.19678, 115.22799 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:35' HOUR TO MINUTE, 115.22799, 115.25854 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:35' HOUR TO MINUTE, 115.25854, 115.28846 FROM DUAL UNION ALL
SELECT DATE '2021-12-20'+INTERVAL '11:35' HOUR TO MINUTE, 115.28846, 115.31776 FROM DUAL;

Then, since both old_data and new_data increase with time, you could use:

SELECT *
FROM   table_name
ORDER BY old_data;

or:

SELECT *
FROM   table_name
ORDER BY new_data;

or, if you want to use the hierarchy then:

SELECT transaction_date,
       old_data,
       new_data
FROM   table_name
START WITH old_data = 0
CONNECT BY
       PRIOR transaction_date <= transaction_date
AND    PRIOR new_data = old_data
ORDER SIBLINGS BY
       transaction_date

Which all output:

TRANSACTION_DATE OLD_DATA NEW_DATA
2021-12-20 11:25:00 0 115.09903
2021-12-20 11:25:00 115.09903 115.13233
2021-12-20 11:25:00 115.13233 115.1649
2021-12-20 11:25:00 115.1649 115.19678
2021-12-20 11:35:00 115.19678 115.22799
2021-12-20 11:35:00 115.22799 115.25854
2021-12-20 11:35:00 115.25854 115.28846
2021-12-20 11:35:00 115.28846 115.31776

fiddle

Upvotes: 1

Related Questions