Isolating with Arrays or SQL?

[ndorfnz]

Ok, here's the deal...

I have LOTS of data stored in a table.

It contains multiple entries for year, distance and time (time being total time ie: 0:34:35 is 34 min and 35 seconds).

This time is stored as varchar, so unless I can convert to time and use min()???

I am trying to create a summary for each year, distance and time. However, I want to isolate to the absolute fastest time for each distance for each year.

So if a distance of 6.2 was in the table 10 times in the year 2000.

I want to only retrieve and print the fastest 6.2 distance and time for year 2000. This would include all the other distances in the table for the fastest time as well.

Ya think this can all be done with a single SQL query? Looks to be pretty complex query if so...

[jkurrle]

Some questions for you:

1) What are the max lengths of time? Hours? Days?
2) Are you actually storing the time in the format HH:MM:SS ?
3) You are looking for, as a result, each unique distance and the fastest time it was covered in, correct? Are there any other things you want displayed?

First, I think you need to change your time format into a raw number, like total seconds. If you do that, I think an SQL query is possible...

[Shrike]

Maybe use some string replacement function in the database to get rid of the ':', then MIN ( field ) should be possible. Far better to use the actual data type though. If the column were a datetime you could do a simple ORDER BY datetime LIMIT 1