Sarah Sarah - 2 months ago 7
SQL Question

Select non-unique id where no row meets criteria

Say I have this table, and I want to select the IDs where all D is < 4. In this case it would only select ID 1 because 2's D>4, and 3 has a D>4

+----+---+------+
| ID | D | U-ID |
+----+---+------+
| 1 | 1 | a |
+----+---+------+
| 1 | 2 | b |
+----+---+------+
| 2 | 5 | c |
+----+---+------+
| 3 | 5 | d |
+----+---+------+
| 3 | 2 | e |
+----+---+------+
| 3 | 3 | f |
+----+---+------+


I really don't even know where to start making a query for this, and my sql isn't good enough yet to know what to google, so I'm sorry if this has been asked before.

Answer

I would simply do:

select id
from table
group by id
where max(d) < 4;

If you happened to want all the original rows, I would use a window function:

select t.*
from (select t.*, max(d) over (partition by id) as maxd
      from t
     ) t
where maxd < 4;