CODEWITHSUNDEEP
sqloracle-database
AMD
AMD

Reputation: 11

How to exclude certain combination pattern in data set?

I have a data set where I have to get rid off the student if he/she has the grade other than "F". here in an example, Brian has grades other than "F", so, Brian should not appear in my result. Only Jack should appear, as, I want EXCLUSIVELY "F" grade students.

I tried it in SQL using NOT IN, EXCEPT functions, but I am not able to resolve the issue (Please click to see the screen shot).

CODE Example

As mentioned above, I am only expecting jack's record, as Brian has grades other than "F". I would really appreciate your help. Thanks.

Upvotes: 1

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If you only want 'Jack', then you can use aggregation:

select name
from t
group by name
having min(grade) = max(grade) and min(grade) = 'F';

If you actually want the original rows, you can use not exists:

select t.*
from t
where not exists (select 1 from t t2 where t2.name = t.name and t2.grade <> 'F');

Upvotes: 2

Related Questions