Search Across Multiple Rows
Assuming the test numbers suggest the order in which the tests are taken and you want athletes having a Negative test coming after the last Positive test of theirs, you can group your table by athleteId and then:


get the last test number where the outcome is 'Negative':

MAX(CASE outcome WHEN 'Negative' THEN test END)

get the last test number where the outcome is 'Positive':

MAX(CASE outcome WHEN 'Positive' THEN test END)

compare the two results to see if the former is greater than the latter:

HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) &gt;
    MAX(CASE outcome WHEN 'Positive' THEN test END)



Full query:

SELECT
  athleteId
FROM
  aTable
GROUP BY
  athleteId
HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) &gt;
    MAX(CASE outcome WHEN 'Positive' THEN test END)
;


If you want athletes who have a Negative test coming after at least one Positive test (not necessarily the last one), you could try a different approach:


get a Negative test;
see if there exists an earlier Positive test for the same athlete.


Or like this in SQL:

SELECT DISTINCT
  athleteId
FROM
  aTable AS neg
WHERE
  outcome = 'Negative'
  AND EXISTS
  (
    SELECT
      *
    FROM
      aTable AS pos
    WHERE
      pos.athleteId = neg.athleteId
      AND pos.outcome = 'Positive'
      AND pos.test &lt; neg.test
  )
;