Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Geeting preceding row with comparisons

SK KFeb 1 2025 — edited Feb 1 2025

Oracle version:


Oracle 19c Enterprise edition Realease 19.0.0.0.0

Create table script :

create table incident_history
(
incident_date TIMESTAMP(6),
incident_id varchar2(15),
inc_desc1 char(15),
inc_desc2 char(15),
inc_desc3 char(15),
inc_desc4 char(15),
inc_desc5 varchar2(255),
inc_start_date DATE,
inc_end_date DATE,
inc_closure_date DATE
);

Insert statements :

Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 6:12:10.304000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31200','PQR            ','TEST2',to_date('31-01-25','DD-MM-RR'),to_date('31-01-25','DD-MM-RR'));
Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 8:46:28.304000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31200','XYZ            ','TEST2',to_date('29-01-25','DD-MM-RR'),to_date('01-02-25','DD-MM-RR'));
Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 9:46:28.304000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31200','ABC1           ','TEST1',to_date('30-01-25','DD-MM-RR'),to_date('01-02-25','DD-MM-RR'));
Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 9:46:28.304000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31202','1WQSA          ','QSD',to_date('29-01-25','DD-MM-RR'),to_date('01-02-25','DD-MM-RR'));
Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 9:46:28.304000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31201','QQQ            ','4TRF5',to_date('29-01-25','DD-MM-RR'),to_date('01-02-25','DD-MM-RR'));
Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 9:46:28.304000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31202','1WQSA          ','QSD',to_date('29-01-25','DD-MM-RR'),to_date('01-02-25','DD-MM-RR'));
Insert into INCIDENT_HISTORY (INCIDENT_DATE,INCIDENT_ID,INC_DESC1,INC_DESC2,INC_START_DATE,INC_END_DATE) values (to_timestamp('31-01-25 9:47:11.122000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'),'31200','KJU            ','TEST2',to_date('01-01-25','DD-MM-RR'),to_date('31-01-25','DD-MM-RR'));

In the incident history table, combination of incident_date and incident_id is always unique.

for a given incident_id, there are multiple rows because it may have rows with different incident dates.
What I need is, for every incident_date, get its previous incident_date and compare all the rows descriptions(inc_desc1,2,3,4,5) and incident dates (start,end and closure date). if row values are same, highlight that they are same. If they are different, highlight that they are different. it should display previous value and current value in the query result.

For a given incident 31200, to get prior row this is the query:

select max(incident_date) from INCIDENT_HISTORY
where incident_id=31200 and incident_date <'31-01-25 9:47:11.122000000 PM';

It gets prior row based on next highest incident_date.

Screenshot

Here are descriptions of columns I wanted in result :

Kindly assist me in writing SQL query for getting the desired result. Thanks a lot in advance.

This post has been answered by Frank Kulash on Feb 1 2025
Jump to Answer

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Feb 1 2025
4 comments
204 views