query help - finding fastest times in dataset

subwayman

I have a table which contains race results for a swimming club.

The table contains the following fields

memberID - ID of swimmer
meetID - ID of Meeting where race too place
eventID - ID of individual race at the meet
stroke - e.g. Freestyle, Breaststroke...
distance - e.g. 50 (metres)
time - swimmers time for the race in seconds.

There is a record for each swimmer for each race they enter at each meet they attend.

What I need to do is extract personal bests (PB's) from this dataset. This is only required for the last Meet entered. To get the PB I need to extract all the records for each event and for each member who raced at that meet calculate the difference between the time for the current event and the fastest previous event of the same stroke and distance.

I have been doing it by running multiple queries in code loops. Can someone come up with a way of doing it in one query?

Thanks,

Cris

Roger_Ramjet

Not sure I'm understanding you here.

Personal bests

$query = "SELECT memberID, stroke, distance, MIN(`time`) as pb FROM table
         GROUP BY memberID, stroke, distance, `time`";

Now you run into the problem that this does not return a unique identifier for the row so you cannot get at the meetID or the eventID. You could also face the problem that the swimmer recorded exactly the same time in more than one race.

Now someone is going to jump in here and sey 'Yes, but in mysql you can select columns not included in the group by clause'.

Quite true:
"MySQL extends the use of GROUP BY so that you can use columns or calculations in the SELECT list that do not appear in the GROUP BY clause. This stands for “any possible value for this group.” "
Except that the manual also sez
"Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You get unpredictable results."
And the meetID is not unique in the group if members swim in more than 1 heat for the race at the same meet.

subwayman

Thanks. Your answer shows me I wasn't clear enough in he original post.

I know how to find the fastest times (PB's). What I should have said (my apologies) is I also need to work out the improvement since the last PB. Hence my need to get the difference between the latest time (whether a PB or not) and the PREVIOUS best time. I need both positive and negative differences. It does not matter if a swimmer swims the same stroke distance combination at a meet (which is often the case). I need the difference for EVERY time recorded for a meet.

Any clearer?

Cheers,

Cris