Link to home
Start Free TrialLog in
Avatar of CMCITD
CMCITDFlag for United States of America

asked on

SQL Select Distinct on one column--but as it applies to each patient in the select statemtn

Here is my query;

select Name, rx.RowUpdateDateTime, rx.VaccineName, ap.PrimaryCareID from AdmVisits AV
left join PhaRxImmunizationData rx
on AV.VisitID = rx.VisitID
LEFT JOIN AdmProviders ap
ON AV.VisitID = ap.VisitID
where rx.VaccineName is not null and
ap.PrimaryCareID IN (
                                    'PHYSICIAN1', 'PHYSICIAN2', 'PHYSICIAN3', 'PHYSICIAN4'
                              )
order by ServiceDateTime desc


When I run this, I get the following;


Name                                     RowUpdateDateTime                           VaccineName              PrimaryCareID
PATIENT 1                              2016-10-31 22:43                                   Tetanus                        Physican1
PATIENT 1                              2016-10-31 22:42                                   Tetanus                        Physican1
PATIENT 2                              2016-10-30 19:43                                   Pneumococcal            Physican1
PATIENT 2                              2016-10-30 19:43                                   Pneumococcal            Physican1


I can tell the system is updating the immunization twice--probably due to a workflow with the nurse, etc.  I don't really care to have it twice though, I just want a single list of each unique immunization the patient received.  So, I'm stuck on getting that distinct select to work ONLY on the immunization name.  I've tried a variety of group by, etc. with no luck.  Thanks for any help!
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Are you basically getting duplicates because of the UpdatedDateTime column?

Could you use something like:
select Name, max(rx.RowUpdateDateTime), rx.VaccineName, ap.PrimaryCareID from AdmVisits AV
left join PhaRxImmunizationData rx
on AV.VisitID = rx.VisitID 
LEFT JOIN AdmProviders ap
ON AV.VisitID = ap.VisitID
where rx.VaccineName is not null and 
ap.PrimaryCareID IN (
                                    'PHYSICIAN1', 'PHYSICIAN2', 'PHYSICIAN3', 'PHYSICIAN4'
                              )
group by Name, rx.VaccineName, ap.PrimaryCareID
order by ServiceDateTime desc

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi CMCITD,

Have you tried the above approaches?

Regards,
Avatar of CMCITD

ASKER

Sorry for the delay in credit--thank you!