Selecting rows from duplicate values, where a field never changed

user_5105703 0
Viewed 23

<table>
  <tr>
    <th>ID</th>
    <th>Test name</th>
    <th>Result</th>
  </tr>
  <tr>
    <td>1</td>
    <td>Performance_1</td>
    <td>PASS</td>
  </tr>
  <tr>
    <td>2</td>
    <td>Performance_1</td>
    <td>FAIL</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Performance_1</td>
    <td>FAIL</td>
  </tr>
    <tr>
    <td>4</td>
    <td>Smoke_9</td>
    <td>FAIL</td>
  </tr>
    <tr>
    <td>5</td>
    <td>Smoke_9</td>
    <td>FAIL</td>
  </tr>
</table>

  • In the above table I want to select all test cases which never passed once.
  • It would return Smoke_9 because it has only failed, never passed.
  • It would not return Performance_1 because even though it failed multiple times, it has passed once.
  • It would be nice if we could SELECT * so I could inspect all fields after.

Thanks

mysql sql
1 Answers

If you want just the TestNames you can group by testname and set the condition in the HAVING clause:

select TestName
from tablename
group by TestName
having sum(Result <> 'FAIL') = 0

If you want all the rows for the TestNames that never passed, you can use NOT EXISTS:

select t.* from tablename t
where not exists (select 1 from tablename where TestName = t.TestName and Result <> 'FAIL')

See the demo.

user_104988280
Related