postgresql Select date from min value

Question

I want to select list of people, who have FIRST time 'E' diagnosis from 2016-01-01.

my code looks like

SELECT DISTINCT ON(form25.visit.patient_id )
        form25.visit.patient_id,
        CONCAT(demographics.party.firstname,' ', demographics.party.lastname) AS pacientas, 
        form25.visit.disease_code,
        demographics.party.code,
        form25.visit.date,
        form25.diagnose.disease_type
    FROM form25.visit
    JOIN demographics.party
        ON form25.visit.patient_id = demographics.party.id
    JOIN form25.diagnose
        ON form25.visit.patient_id = form25.diagnose.card_id
    WHERE form25.visit.disease_code LIKE 'E%'
        AND form25.diagnose.disease_type = '+'
        AND form25.visit.date >= '2016-01-01'
    ORDER BY form25.visit.patient_id, form25.visit.date ASC

result i get:

patients E.. 2013,2014,2016, 2017

(contains E.. with 2016 so I see them)

All I want is to eliminate those who have date(s) with E.. before 2016, and see only those who had it first time from 2016.

Gordon Linoff · Accepted Answer · 2017-03-31 13:50:42Z

1

First, your query would be much easier to read (and write) if you used table aliases.

Second, you can get what you want by doing an aggregation and then looking at the minimum date:

SELECT v.patient_id, CONCAT(p.firstname, ' ', p.lastname) AS pacientas, 
       MIN(v.date) as first_date
FROM form25.visit v JOIN
     demographics.party p
     ON v.patient_id = p.id JOIN
     form25.diagnose d
     ON v.patient_id = d.card_id
WHERE v.disease_code LIKE 'E%' AND d.disease_type = '+'
GROUP BY patient_id, pacientas
HAVING MIN(v.date) >= '2016-01-01';

Note that this gives you the patients that meet the conditions. If you need visit details, you can join back or use window functions.

answered Mar 31, 2017 at 13:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Georgi Raychev Over a year ago

Just for my records. how would :

AND NOT EXISTS ( 	select 1 	from form25.visit v1 	where form25.visit.date = v1.visit.date and v1.visit.date <= '2015-12-31')

Compare to your solution in terms of performance?

Collectives™ on Stack Overflow

postgresql Select date from min value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related