Find most recent record

Question

I have a data-set like:

org_id  patient_id  date
313     455259      2018-01-07
313     455259      2018-04-09
679     455259      2018-07-08
780     455259      2018-09-06

This patient was at the same org in the first two claims. After that, they changed orgs. How I find the first org they change to? For example, this is the output I am looking for -

org     patient     new_org
313     455259      679

Answer 1

I mean the query below can help you:

with data as (
  -- get records with previous value of org_id
  select 
    patient_id,
    org_id,
    date,
    lag(org_id) over (partition by patient_id order by date) prev_org_id    
  from tbl
) 
-- select records where org_id changed
select * from data
where org_id != prev_org_id and prev_org_id is not null;

PostgreSQL window functions fiddle

Answer 2

This solution works for multiple patients.

With A As (
Select patient_id, org_id, 
       Lead(org_id) Over (Partition by patient_id Order by date, org_id) As new_org,
       Row_Number() Over (Partition by patient_id Order by date, org_id) As sort
From Tbl
),
B As (
Select patient_id, org_id, new_org,
       Row_Number() Over (Partition by patient_id Order by sort) As num
From A
Where org_id<>new_org
)
Select patient_id, org_id, new_org
From B
Where num=1

Output:

patient_id | org_id | new_org
___________|________|_________
   455259  |   313  |   679 

Answer 3

This should work for all edge cases.

WITH data AS (
SELECT *, 
       LAG(org_id, 1, org_id) OVER (PARTITION BY patient_id ORDER BY date) AS prev_org,
       (org_id <> LAG(org_id, 1, org_id) OVER (PARTITION BY patient_id ORDER BY date)) :: int AS flag
FROM tb1
),
agg_data AS (
SELECT *, 
       MIN(CASE flag WHEN 1 THEN date END) OVER (PARTITION BY patient_id) AS min_date
FROM data
)
SELECT prev_org AS org, patient_id AS patient, org_id AS new_org
FROM agg_data
WHERE date = min_date;

See Demo