Question: How to select from a single table with a condition across multiple rows of the same column

Question

How to select from a single table with a condition across multiple rows of the same column

Answers 3
Added at 2016-12-27 18:12
Tags
Question

I have a table that includes columns for medical numbers and procedures. There are a lot of rows and medical numbers can be repeated with many procedures; for example:

Mid_no    procedure
--------------------
100.          20
100.          30
200.          30

I want to select all mid_no that have procedure 30 and do not have procedure 20. In the above example, the desired result would be:

Mid_no.    Procedure
---------------------
200.        30
Answers to

How to select from a single table with a condition across multiple rows of the same column

nr: #1 dodano: 2016-12-27 18:12
SELECT t.mid_no,
  t.procedure
FROM TABLE t
WHERE NOT EXISTS
  (SELECT 1 FROM TABLE t1 WHERE t1.mid_no = t.mid_no AND t1.procedure = 20
  )
AND t.procedure = 30;
nr: #2 dodano: 2016-12-27 18:12
select mid_no from table where procedure = 30
intersect
select mid_no from table where procedure != 20
nr: #3 dodano: 2016-12-27 23:12

With a single scan of your table, you can use:

select distinct Mid_no
from (
        select count(case when procedure=20 then 1 end ) over ( partition by Mid_no) as has20,
               count(case when procedure=30 then 1 end ) over ( partition by Mid_no) as has30,
               Mid_no
        from yourTable
     )
where has20 = 0 
  and has30 != 0

The internal query counts the number of occurrences of 20 and 30 in two different values, for each Mid_no; the external one simply picks the only records with no occurrence of 20 and at least one occurrenxe of 30.

Source Show
◀ Wstecz