CODEWITHSUNDEEP
sqlpostgresql
Take2
Take2

Reputation: 159

Select only IDs where certain columns only contains a certain value

I have the following,

CREATE TABLE t (
  id          int,
  events      text, -- may be 'HR1', 'HR2', 'HR3', etc Or 'BP'
  event_value int
);

I basically want to select IDs which have, for HR1 or HR2 (depending on which it is used for that ID), all their values in between 50 and 110.

The query for HR1 or HR2 returns a distinct id and event if all the groups values are between 50 and 110.

The following table is an example:

ID         |  event  | event value
-----------+---------+-------------
     1        'HR1'       80
     1        'HR1'       90
     1        'HR1'       72
     1        'HR1'       91
     1        'HR1'       69
     1        'BP'        2.3
-
     2        'HR1'       90
     2        'HR1'       40
     2        'HR1'       39
-
     3        'HR2'       200
     3        'HR2'       230
     3        'HR2'       85
-
     4        'HR2'       90
     4        'HR2'       80
     4        'HR2'       90

I want the following output:

subject_id  | event
------------+--------
     1        'HR1'
     4        'HR2'

Upvotes: 1

Views: 1290

Answers (4)

D-Shih
D-Shih

Reputation: 46219

I would use NOT EXISTS and DISTINCT subject_id,event to make it. 

SELECT DISTINCT id,event  
FROM t AS t1
WHERE NOT EXISTS(
    SELECT 1 
    FROM t AS t2
    WHERE (t2.event_value < 50 OR t2.event_value > 110)
        AND t1.id = t2.id
        AND t1.event = t2.event
)
AND t1.event in ('HR1','HR2');

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You have changed the desired output, which makes this a completely different request. You want one row per subject now, an aggregation:

SELECT id, event
FROM t
WHERE event in ('HR1', 'HR2')
GROUP BY id, event
HAVING count(CASE WHEN event_value NOT BETWEEN 50 AND 110 THEN 1 END) = 0
ORDER BY id;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

This answer is no longer valid, as the OP has altered the requirement.

No, this is not complex. You want to exclude subjects for which exist HR1/2 records with a value outside the given bounds. So use NOT EXISTS or NOT IN.

select *
from mytable
where subject_id not in
(
  select subject_id
  from mytable
  where event in ('HR1', 'HR2')
  and event_value not between 50 and 100
)
order by subject_id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Thorsten's answer is fine, but I would express it as:

select subject_id,
       min(event) -- only one event per subject
from mytable
where event in ('HR1', 'HR2')
group by subject_id
having min(event_value) >= 50 and
       max(event_value) <= 110
order by subject_id;

Upvotes: 2

Related Questions