CODEWITHSUNDEEP
mysqlsql
Joseph Hailu
Joseph Hailu

Reputation: 487

SQL using group by to select groups where all records in a group have a certain value

Here is some sample data. I am trying to group by OrderID and lineID and then attempt to return only the unique orderID and lineID from groups that have 'YES' for ALL records. The primary key is a composite of OrderID lineID and positionID

  OrderID        lineID  positionID   fieldOfInterest    somefield1   somefield2 ....
    1A2          10248       1          'YES'               -           -
    1A2          10248       2          'YES'               -           -
    1A2          10248       3          'YES'               -           -
    1A2          10249       2          'YES'               -           -
    1C3          11200       5          'YES'               -           -
    1C3          10250       1          'NO'                -           -
    1V8          10250       7          'YES'               -           -
    1V8          10250       8          'NO'                -           -
    .             .          .           .                  .           .
    .             .          .           .                  .           .
    .             .          .           .                  .           .

Result that I am looking for would be 

  OrderID        lineID  
    1A2          10248       <------ all records (3) had 'YES'
    1A2          10249       <------ all records (1) had 'YES'
    1C3          11200       <------ all records (1) had 'YES'

any insights would be appreciated.

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use aggregation:

select OrderID, lineID
from t
group by OrderID, lineID
having min(fieldOfInterest) = max(fieldOfInterest) and min(fieldOfInterest) = 'YES';

Actually, if the values are only "YES" and "NO", you can do:

having min(fieldOfInterest) = 'YES'

Upvotes: 1

Related Questions