Is this possible in one query?

[chiprivers]

I have a database holding statistical data regarding the results of horse racing.  I want to pull records, one per race, showing details of the horse in each race that was the favourite.  The table that hold the details of the runners in each race is detailed below:

 

CREATE TABLE runners (
runnerID int unsigned not null auto_increment primary key,
raceREF int,
position int,
other_result varchar(100),
horseREF int,
trainerREF int,
age int,
stones int,
pounds int,
jockeyREF int,
days int,
spx int,
spy int
)

 

The relevent fields for the purpose of this query are:

 

runnerID => this is clearly the unique key for the records

raceREF => this is reference to the unique key of records in the races table

position => this details the finishing position of the horse in the race

spx and spy => these are the two values that make up the odds ie 2/1 would be spx = 2, spy =1

 

Now what I havn't got in my runners table is a column the indicates whether the horse was the favourite, what I first need to do is look at the spx and spy columns and work out which is the lowest odds.  I can use the following query to do this but it would have to be done for each race at a time:

 

SELECT r.* FROM runners AS r WHERE raceREF = $raceID ORDER BY (spx / spy) ASC LIMIT 1

 

I believe this is correct to return one record for the given race detailing the shortest price horse but I need to run this query for each race.

 

What I want to be able to do is run one query through the database to return this information for each race, something along the lines of:

 

SELECT r.* FROM runners AS r GROUP BY raceREF

 

But I need to add something to ensure that the record returned for each raceREF is the horse with the shortest odds.  I had posted an earlier thread asking about how to do this in a more general form but I didn't really get a solution, I am hoping that by giving the full details of what I am trying to do somebody may be able to solve this problem.  Because there is so much data in my database, the option I detailed above querying one race at a time, is just overloading my server!

[LazyJones]

min-function might work for you

 

http://www.tizag.com/mysqlTutorial/mysqlmin.php

 

that URL has a good example for your case

[chiprivers]

min-function might work for you

 

http://www.tizag.com/mysqlTutorial/mysqlmin.php

 

that URL has a good example for your case

 

From what I understand of the MIN() function, I could do:

 

SELECT raceREF, MIN(spx/spy) FROM runners GROUP BY raceREF

 

however this would only return me the shotest odds for each race and I do not think, from what I understand of the MIN() function, that you can use this also to return the values held within the same record as the minimum odds.  Perhaps someone could confirm this?