Compare Multiple rows In SQL Server

Question

I have a SQL Server database full of the following (fictional) data in the following structure:

ID | PatientID | Exam | (NON DB COLUMN FOR REFERENCE)
------------------------------------
1  | 12345     | CT   | OK
2  | 11234     | CT   | OK(Same PID but Different Exam)
3  | 11234     | MRI  | OK(Same PID but Different Exam)
4  | 11123     | CT   | BAD(Same PID, Same Exam)
5  | 11123     | CT   | BAD(Same PID, Same Exam)
6  | 11112     | CT   | BAD(Conflicts With ID 8)
7  | 11112     | MRI  | OK(SAME PID but different Exam)
8  | 11112     | CT   | BAD(Conflicts With ID 6)
9  | 11123     | CT   | BAD(Same PID, Same Exam)
10 | 11123     | CT   | BAD(Same PID, Same Exam)

I am trying to write a query with will go through an identify everything that isn't bad as per my example above.

Overall, a patient (identified by PatientId) can have many rows, but may not have 2 or more rows with the same exam!

I have attempted various modifications of exams I found on here but still with no luck.

Thanks.

How do we know what "isn't bad"? Whats "(NON DB COLUMN FOR REFERENCE)"? — jarlh
– jarlh, Commented Feb 13, 2015 at 12:55
@jarlh: That is something I added as part of the question to give you an indication of what is considered "ok" and what is "bad" and the reason for it being bad. — tornup
– tornup, Commented Feb 13, 2015 at 12:56

Gordon Linoff · Accepted Answer · 2015-02-13 12:55:13Z

3

You seem to want to identify duplicates, ranking them as good or bad. Here is a method using window functions:

select t.id, t.patientid, t.exam,
       (case when cnt > 1 then 'BAD' else 'OK' end)
from (select t.*, count(*) over (partition by patientid, exam) as cnt
      from table t
     ) t;

answered Feb 13, 2015 at 12:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Deep · Accepted Answer · 2015-02-13 12:58:19Z

0

use Count() over() :

select *,case when COUNT(*) over(partition by PatientID, Exam) > 1 then 'bad' else 'ok' 
from yourtable

answered Feb 13, 2015 at 12:58

Deep

3,2021 gold badge15 silver badges21 bronze badges

Comments

Vishal Gajjar · Accepted Answer · 2015-02-13 13:07:53Z

0

You can also use:

 ;WITH  CTE_Patients
      (ID, PatientID, Exam, RowNumber)
AS
(
      SELECT      ID, PatientID, Exam
                  ROW_NUMBER() OVER (PARTITION BY PatientID, Exam ORDER BY ID)
      FROM        YourTableName
)
SELECT      TableB.ID, TableB.PatientID, TableB.Exam, [DuplicateOf] = TableA.ID
FROM        CTE_Patients TableB
INNER JOIN CTE_Patients TableA
            ON    TableB.PatientID = TableA.PatientID
            AND   TableB.Exam = TableA.Exam
WHERE       TableB.RowNumber > 1 -- Duplicate rows
AND         TableA.RowNumber = 1 -- Unique rows

I have a sample here: SQL Server – Identifying unique and duplicate rows in a table, you can identify unique rows as well as duplicate rows

edited Feb 13, 2015 at 13:07

answered Feb 13, 2015 at 13:02

Vishal Gajjar

1,0279 silver badges20 bronze badges

Comments

Christian Phillips · Accepted Answer · 2015-02-13 13:09:21Z

0

If you don't want to use a CTE or Count Over, you can also group the Source table, and select from there...(but I'd be surprised if @Gordon was too far off the mark with the original answer :) )

SELECT  a.PatientID, a.Exam, CASE WHEN a.cnt > 1 THEN 'BAD' ELSE 'OK' END
FROM    ( SELECT    PatientID
                   ,Exam
                   ,COUNT(*) AS cnt
          FROM      tableName
          GROUP BY  Exam
                   ,PatientID
        ) a

answered Feb 13, 2015 at 13:09

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

Comments

jarlh · Accepted Answer · 2015-02-13 13:22:16Z

0

Select those patients that never have 2 or more exams of same type.

select * from patients t1
where not exists (select 1 from patients t2
                  where t1.PatientID = t2.PatientID
                  group by exam
                  having count(*) > 1)

Or, if you want all rows, like in your example:

select ID,
       PatientID,
       Exam,
       case when exists (select 1 from patients t2
                         where t1.PatientID = t2.PatientID
                         group by exam
                         having count(*) > 1) then 'BAD' else 'OK' end
from patients

edited Feb 13, 2015 at 13:22

answered Feb 13, 2015 at 13:17

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

Compare Multiple rows In SQL Server

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related