CODEWITHSUNDEEP
postgresql
pvand
pvand

Reputation: 103

How can I get only one row if only one column is different

I have a table with patient ID's, contact dates and actioncodes. I want to retrieve all rows with actioncodes equal to EPS or D, however i want to keep only one row if the actioncode exists on the same contactdate.

For example this is part of my table, journal:

PatientID  Contactdate  Actioncode
1          2010-5-6     EPS
1          2010-5-6     D
1          2012-3-4     CNT
1          2013-7-8     D
2          2010-1-4     EPS
2          2010-5-6     D

This is the code i have now to retrieve all rows where actioncode is either EPS or D

select * from journal j where j.actioncode in ('EPS', 'D')

I tried group by contactdate, but then i miss the rows where the patients are different. The same effect occurs with distinct(contactdate). What can i use here to return only one row when the date and the patientid are similar and the actioncode is either D or EPS?

Preferred outcome:

PatientID   Contactd   Actioncode
1           2010-5-6   D
1           2012-3-4   D
2           2010-1-4   EPS
2           2010-5-6   D

Upvotes: 1

Views: 583

Answers (3)

forpas
forpas

Reputation: 164069

You can do it with UNION ALL for the 2 cases:

select * from journal where actioncode = 'D'
union all
select * from journal j where j.actioncode = 'EPS'
and not exists (
  select 1 from journal
  where PatientID = j.PatientID and Contactdate = j.Contactdate and actioncode = 'D'
)

The 2nd query will only fetch rows if the 1st query returns nothing for actioncode = 'D'.
See the demo.
Results:

> patientid | contactdate | actioncode
> --------: | :---------- | :---------
>         1 | 2010-05-06  | D         
>         1 | 2013-07-08  | D         
>         2 | 2010-05-06  | D         
>         2 | 2010-01-04  | EPS

Upvotes: 1

nunoq
nunoq

Reputation: 550

What you want is a GROUP BY two columns: PatientID and Contactdate. You can use MAX() or MIN() to select one of the rows.

select
j.PatientID,
j.Contactdate,
MIN(j.actionCode)
from 
journal j 
where j.actioncode in ('EPS', 'D')
group by j.PatientID, j.Contactdate

For match your preferred outcome, you should use MIN().

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

We can try using ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY PatientID, Contactdate
                                 ORDER BY Actioncode) rn
    FROM journal
    WHERE Actioncode in ('EPS', 'D')
)

SELECT PatientID, Contactdate, Actioncode
FROM cte
WHERE rn = 1;

This arbitrarily would always retain the Actioncode='D' record, should both action codes appear. If instead you would want to retain the EPS record, then modify the call to ROW_NUMBER to use ORDER BY Actioncode DESC.

Upvotes: 2

Related Questions