How to remove first row in every grouped result ordered on date column in postgresql

user_2884859 0
Viewed 547

I have a sql data (table) which consists of n number of columns, but 3 are of particular interest

claim_id patient_id admission_date
1 p_1 2001-01-01
2 p_2 2001-02-01
3 p_1 2001-01-01
4 p_3 2001-02-02
5 p_2 2001-03-01
6 p_2 2001-04-01
7 p_1 2001-04-01

What I want is remove first row (based on admission_date) of every patient_id. In case there are two rows for any patient on his/her first date of admission (e.g. patient_id = p_1 having 2 rows 1 & 3 with same dates of admission) then remove all those rows; and get an output like this

claim_id patient_id admission_date
5 p_2 2001-03-01
6 p_2 2001-04-01
7 p_1 2001-04-01

I am not very well conversant with sql, but I can do that easily in R using duplicated. Is something like that, a memory efficient (I have to filter 10 million rows at least) is available in POSTGRESQL?

What I have tried? Basically nothing in sql. If that data was of less size I could have exported it in R and have done my analysis there.

Please help.

postgresql-12 postgresql sql
2 Answers

You can use window functions for that:

select claim_id, patient_id, admission_date
from (
  select claim_id, patient_id, admission_date, 
         dense_rank() over (partition by patient_id order by admission_date) as rn
   from the_table
) t 
where rn > 1;

dense_rank() creates sequential numbers for each patient_id sorted by the admission_date. If two rows have the same admission_date, they will get the same "rank". The WHERE clause in the outer query simply removes the "first" row for each patient.

user_3303150

We can use exists logic here:

SELECT claim_id, patient_id, admission_date
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
              WHERE t2.patient_id = t1.patient_id AND
                    t2.claim_id < t1.claim_id);

Given that some patients seems to have mutilple admissions on the same date, I am using the claim_id as an ordered identifier to determine what admission came first.

If performance of the above be a concern, then the following index should speed things up substantially:

CREATE INDEX idx ON yourTable (patient_id, claim_id);
user_18632290
Related