CODEWITHSUNDEEP
sqloracle-database
Krishna
Krishna

Reputation: 3

Oracle SQL Current and previous status in the same output record

I have a weird requirement below to display the current state of an application and the previous state, my requirements in a picture

I have tried to get the top value of each application and the remaining set separately using SQL but I'm not sure of the best way to combine them. But I am sure there are easier ways to do this.

Pasting my query here.

Query 1 gives me the latest status of each application:

select application_id, last_updated, application_state
from BELL_APPLICATION_EVENTS where (application_id, last_updated) in (
    select application_id, max(last_updated) as last_updated
    from BELL_APPLICATION_EVENTS
    group by application_id
) order by last_updated desc ;

The below query provides the data set for rest of the statuses, such as "Application finalized" and "User Email Sent" as shown in the picture separately.

select *
from BELL_APPLICATION_EVENTS U1
where last_updated < (
      select max(last_updated) 
      from BELL_APPLICATION_EVENTS where application_id = U1.application_id)
order by U1.LAST_UPDATED desc ;

Could you please help to provide an easier option to get the current state and previous state in a single record per application id?

Upvotes: 0

Views: 448

Answers (1)

kevinskio
kevinskio

Reputation: 4551

The LAG analytic function is perfect for this. Please use SQL Fiddle instead of data in pictures to provide a test case. From the documentation:

   SELECT last_name, hire_date, salary,
   LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
   FROM employees
   WHERE job_id = 'PU_CLERK';

Upvotes: 5

Related Questions