Search code examples
sqlsql-servergexperts

Need SQL Script

incident_number   Has_Breached   Id
INC15229006       Breached       1
INC15229006       Not Breached   2
INC15229006       Not Breached   3
INC15087939       Not Breached   4
INC15087939       Not Breached   5
INC15087939       Not Breached   6
INC15087939       Not Breached   7
INC15087939       Breached       8
INC15087939       Not Breached   9
INC15087939       Breached       10
INC15087940       Not Breached   13
INC15087940       Not Breached   12
INC15087940       Not Breached   24
INC15087940       Not Breached   32
INC15087940       Not Breached   45
INC15087940       Not Breached   67

I need results like below

incident_number   Has_Breached
INC15229006       Breached
INC15087939       Breached
INC15087940       Not Breached
Solution

  • If the possible values are only 'Breachend' and 'Not Breached', you can GROUP BY incident_number and get the minimum as 'Breached' < 'Not Breached'.

    SELECT incident_number,
       min(has_breached) has_breached
       FROM elbat
       GROUP BY incident_number;