CODEWITHSUNDEEP
sqloracle-database
Mary
Mary

Reputation: 1

Using SQL to pull prior record

I have some transactional data in an Oracle database. Within this table the same id_num can be associated with multiple objects occurring at different dates/times. I am trying to pull together results using SQL that contain the most recent record for each id_num and also the previous object value. This screen shot shows an example of the data along with the desired results. I color coded the data to illustrate which rows within the data the result should be pulled from.

I wrote the following, which gets me the most recent record for each id_num, but I am struggling with how to pull in the previous_object. Really appreciate any help anyone can provide.

data set and desired result

SELECT * FROM 

(
SELECT
  id_num,
  date_time,
  object, 
  RANK() OVER (PARTITION BY id_num ORDER BY date_time DESC) RANK_NUM

FROM TableX

  ) x 

WHERE RANK_NUM = 1

Upvotes: 0

Views: 48

Answers (1)

DannySlor
DannySlor

Reputation: 4620

select   id_num
        ,object
        ,date_time
        ,previous_object
from     (
         select   id_num
                 ,object
                 ,max(date_time)                                                      as date_time
                 ,lag(object) over(order by max(date_time))                           as previous_object
                 ,row_number() over(partition by id_num order by max(date_time) desc) as rn
         from     t
         group by id_num, object
         ) t
where    rn = 1
ID_NUM OBJECT DATE_TIME PREVIOUS_OBJECT
1X C 27-JUL-22 14.43.00.000000 B
2X G 10-AUG-22 15.36.00.000000 F

Fiddle

Upvotes: 1

Related Questions