MySQL Report

[wildasIwanabe]

Hi,

 

I've been playing around with some data in MySQL.  I can manage to enter the data with no problem but I'm a having a little problem trying to generate a report from the data.

 

Basically, the data contains 3 fields.  Date, distance ran and Time taken.  Whenever I go for a run, I enter my results into the mysql table.  I then have a script to get the best time for each distance ran.  That part works great, but the date that the best time was ran is wrong.  Please see my attachment for a better understanding.  I have several 5km runs, and my best time was in June, but it shows that it was in April.

 

This is the query I wrote in PHP...

$query = "SELECT MIN(time), distance, rundate FROM running group by distance";

 

Am I missing something from the query ???

 

thanks.

 

 

 

 

 

 

[attachment deleted by admin]

[Gamic]

Something like this may get the results that you want:

select allRuns.* from(
select time, distence, date from location 
)allRuns join(
select min(time) as time, distence 
from running 
group by distance
) minDistence 
on      allRuns.time = minDistence.time 
	and allRuns.distence = minDistence.distence

Although I'm sure there is a better way, I just can't think of it right now.

[wildasIwanabe]

Thanks Gamic.  That works.  Wow, I didn't realize it had to be that complicated. 

 

If you don't mind, can you please explain the syntax to me.  I don't understand that minDistance part.  Sorry, I'm still new at this.