Search Across Multiple Rows I have a database that is full of doping results for athletes.

+-----------+------+----------+
| athleteId | test | outcome  |
+-----------+------+----------+
|    100011 |    1 | Pending  |
|    100012 |    1 | Positive |
|    100012 |    2 | Negative |
|    100013 |    2 | Positive |
|    100013 |    3 | Negative |
|    100014 |    2 | Positive |
|    100022 |    1 | Negative |
|    100022 |    2 | Positive |
|    100022 |    3 | Positive |
|    100023 |    2 | Negative |
|    100030 |    1 | Negative |
|    100031 |    2 | Disputed |
|    100033 |    4 | Pending  |
|    100036 |    5 | Positive |
|    100037 |    5 | Positive |
|    100038 |    5 | Positive |
|    100051 |    1 | Pending  |
|    100054 |    1 | Positive |
+-----------+------+----------+


What I'm needing to return is the athleteId where a test returned a positive, and then another test returned a negative.
That narrows it down to athleteIds 100012, 100013, 100022, but I'm not sure how to structure a query to return this.

Any help is greatly appreciated!
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
  )
;