John Gordon John Gordon - 4 months ago 6
SQL Question

sql how to select the lack of a condition?

I have this table structure and data for keeping track of horse race results:

T_RACE_HISTORY
==============
HORSE_ID RACE_DT PLACE
-------- ---------- -----
1 2014-05-03 1
1 2014-07-22 1
1 2016-06-10 3
2 2016-06-10 2
3 2016-06-10 1


I want a query that returns each unique horse id and either the date of the latest race won by that horse, or null if the horse has never won.

In other words, I want a query with this output:

HORSE_ID RACE_DT
-------- ----------
1 2014-07-22
2 (null)
3 2016-06-10


I can get the winning horses with a query like this:

SELECT HORSE_ID,
MAX(RACE_DT)
FROM T_RACE_HISTORY
WHERE PLACE = 1
GROUP BY HORSE_ID


But I have no idea how to look for the lack of any won races.

Answer

You can use conditional aggregation:

select horse_id,
       max(case when place = 1 then race_dt end)
from t_race_history
group by horse_id