Remove duplicate rows after joining multiple tables

Question

I have a query that will eliminate the duplicates and only shows the unique records.

SELECT DISTINCT TBLPATIENT.MRN
FROM tblpatient

Output:

However, I have additional columns that I need to show up but I want only the unique MRNs.

SELECT 
      v.PatientID,
      p.firstname, 
      p.lastname, 
      p.dob, 
      p.mrn, 
      s.VisitID,
      v.AdmitedDate
 FROM
     tblPatient p
 JOIN 
     tblPatientVisit v ON p.id = v.PatientID
 JOIN 
     tblPatientSmokingScreenOrder s ON v.id = s.VisitID
 JOIN 
     DESCRIPTOR t ON s.SMOKING_STATUS_DSC_ID = DESCRIPTOR_ID
 WHERE 
      isdate(p.DOB) = 1 
      AND CONVERT(date,p.DOB) <'12/10/2000' 
      AND v.PatientType = 'I' and 
      isdate(v.AdmitedDate) = 1 
      AND CONVERT(date,v.AdmitedDate) > '06/16/2013 16:16'

Output:

PatientID   firstname   lastname    dob         mrn     VisitID AdmitedDate 
---------------------------------------------------------------------------
1           JAMES       TEST        6/11/1942   100241  1       54:00.0 
10          TEST3       DEMOGRAPHIC 4/7/1980    100251  13      39:00.0 
5           TEST2       DEMOGRAPHIC 8/31/1938   3       12      36:00.0 
21          ZACHARY     EFRON       11/2/1976   100267  24      11:00.0 
16          PATIENT     DEMO        2/28/1943   100260  26      56:00.0 
17          ALICE       WONDERLAND  9/20/1942   100261  20      14:00.0
23          TEST5       BROWN       5/6/1965    15285   27      40:00.0
23          Test5       BROWN       5/6/1965    15285   27      40:00.0

How do I take the script above and refactor it to only show Test5 Brown once?

Can you provide all rows, from each table, relating to patient #23, and visit 27? Which table is it that has 2 rows? One would think that `tblpatient` only has one row per patient. `tblpatientvisit` likely has 2+ rows for patients with 2+ visits (however it's unclear whether it has 2+ rows for the same visit, due to other descriptive columns, could be your problem), and it is unclear also whether there might be a 1:M relationship between `tblpatientvisit` and `tblpatientsmokingscreenorder`. Using `distinct` would just be an attempt to bandaid the query. We need to know where the 1:M rel. is — Brian DeMilia, Jun 20 '15 at 02:19
Didn't notice that the names are spelled differently for patient #23. Perhaps `tblpatient` has effective dated rows reflecting changes to a patient's name and other info over time. What fields are on that table? Perhaps you need a correlated subquery to show the max effective dated record as of the visit. — Brian DeMilia, Jun 20 '15 at 02:21
possible duplicate of [How to query SQL Table and remove duplicate rows from a result set](http://stackoverflow.com/questions/16327383/how-to-query-sql-table-and-remove-duplicate-rows-from-a-result-set) — Tab Alleman, Jun 22 '15 at 15:42
I updated the title to better reflect the question and updated the tags and minor rewording for better readability. — Ram, Jun 22 '15 at 21:32

Tim3880 · Answer 1 · 2015-06-20T02:03:25.747

If you don't have any unique value, you may need the ROW_NUMBER to help find the first record of each unique key:

 SELECT * FROM (
  SELECT 
  v.PatientID,
  p.firstname, 
  p.lastname, 
  p.dob, 
  p.mrn, 
  s.VisitID,
  v.AdmitedDate,
  ROW_NUMBER() OVER (PARTITION BY p.mrn ORDER BY AdmitedDate DESC) AS row_nm
  FROM
    tblPatient p
    JOIN tblPatientVisit v ON p.id = v.PatientID
    JOIN tblPatientSmokingScreenOrder s ON v.id = s.VisitID
    JOIN DESCRIPTOR t ON s.SMOKING_STATUS_DSC_ID = DESCRIPTOR_ID
  WHERE 
    isdate(p.DOB) = 1 
    AND CONVERT(date,p.DOB) <'12/10/2000' 
    AND v.PatientType = 'I' and 
    isdate(v.AdmitedDate) = 1 
    AND CONVERT(date,v.AdmitedDate) > '06/16/2013 16:16'
 ) res
 WHERE row_nm = 1

Here you need control which patient to show up with the mrn by using different ORDER BY column(s) in the ROW_NUMBER() function.

If you have an unique value (unique to mrn, e.g. admiteddate), you can use group by to find the min/max(value) for each mrn, and do a join to get the other values like :

 SELECT * from Table JOIN 
   (Select mrn, MAX(admitedDate) max_date from Table ) u
 ON Table.mrn = u.mrn AND Table.AdmitedDate = u.max_date

Martin Noreke · Answer 2 · 2015-06-21T13:42:46.073

-1

You probably just need to use the distinct keyword.

SELECT DISTINCT
  v.PatientID,
  p.firstname, 
  p.lastname, 
  p.dob, 
  p.mrn, 
  s.VisitID,
  v.AdmitedDate

FROM
  tblPatient p
  JOIN tblPatientVisit v ON p.id = v.PatientID
  JOIN tblPatientSmokingScreenOrder s ON v.id = s.VisitID
  JOIN DESCRIPTOR t ON s.SMOKING_STATUS_DSC_ID = DESCRIPTOR_ID

WHERE 
  isdate(p.DOB) = 1 
  AND CONVERT(date,p.DOB) <'12/10/2000' 
  AND v.PatientType = 'I' and 
  isdate(v.AdmitedDate) = 1 
  AND CONVERT(date,v.AdmitedDate) > '06/16/2013 16:16'    
--    order by MRN

This will return all unique rows.

edited Jun 21 '15 at 13:42

answered Jun 20 '15 at 01:37

Martin Noreke

4,066
22
34

Perhaps they thought the collation was case sensitive in which case, this would not work. BTW having the group by and distinct seems redundant. It probably won't affect anything to have both, but each should work just fine without the other. – Stephan Jun 21 '15 at 05:25
The above query produces the following error: "Column 'dbo.tblPatient.MRN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." As Stephan said, you should remove the GROUP BY clause, because the DISTINCT keyword does the same thing (considering the absence of any aggregate function). – Razvan Socol Jun 21 '15 at 05:35
You guys are correct. I was tired when I wrote that one up by hand. I'll edit the answer. – Martin Noreke Jun 21 '15 at 13:42

Remove duplicate rows after joining multiple tables

2 Answers2