0 votes
Is there a way to extract historical horse data and where a horse finished (and/or lengths beaten / won) in the next race, assuming there is one?
by jnestor3 Novice (440 points)

1 Answer

0 votes
Yes, a number of ways, however it depends on the nature of your query and what you want to do with the data afterwards.

If it is just one horse, then a query for the results of all that horses' races including lenghhs beaten would suffice.

For example,

mysql> select scheduled_time, course, distance_yards, distance_behind_winner, distance_won from historic_races_beta join historic_runners_beta using (race_id) where name="Frankel";

+---------------------+-------------+----------------+------------------------+--------------+

| scheduled_time      | course      | distance_yards | distance_behind_winner | distance_won |

+---------------------+-------------+----------------+------------------------+--------------+

| 2010-08-13 18:35:00 | Newmarket   |           1760 |                   NULL |         0.50 |

| 2010-09-10 16:25:00 | Doncaster   |           1540 |                   NULL |        13.00 |

| 2010-09-25 14:30:00 | Ascot       |           1760 |                   NULL |        10.00 |

| 2010-10-16 14:25:00 | Newmarket   |           1540 |                   NULL |         2.25 |

| 2011-04-16 15:10:00 | Newbury     |           1540 |                   NULL |         4.00 |

| 2011-04-30 15:10:00 | Newmarket   |           1760 |                   NULL |         6.00 |

| 2011-06-14 15:45:00 | Royal_Ascot |           1760 |                   NULL |         0.75 |

| 2011-07-27 15:10:00 | Goodwood    |           1760 |                   NULL |         5.00 |

| 2011-10-15 15:35:00 | Ascot       |           1760 |                   NULL |         4.00 |

| 2012-05-19 15:40:00 | Newbury     |           1760 |                   NULL |         5.00 |

| 2012-06-19 14:30:00 | Royal_Ascot |           1760 |                   NULL |        11.00 |

| 2012-08-01 15:10:00 | Goodwood    |           1760 |                   NULL |         6.00 |

| 2012-08-22 15:40:00 | York        |           2200 |                   NULL |         7.00 |

| 2012-10-20 16:05:00 | Ascot       |           2200 |                   NULL |         1.75 |

+---------------------+-------------+----------------+------------------------+--------------+

14 rows in set (2.47 sec)

In this case, since Frankel was never beaten, we have used distance_won, and all distance_beaten values are consequently NULL.
by colin Frankel (19.7k points)
...