I am looking to compare two sets of data that are stored in the same table. I am sorry if this is a duplicate SO post, I have read some other posts but have not been able to implement it to solve my problem.
I am running a query to show all Athletes and times for the most recent date (2017-05-20):
SELECT `eventID`, `location`,<BR> `date`, `barcode`, `runner`, `Gender`, `time` FROM `TableName` WHERE `date`='2017-05-20'
I would like to compare the time achieved on the 20th May with the previous time for each athlete.
SELECT `time` FROM `TableName` WHERE `date`='2017-05-13'
How can I structure my query showing all of the ATHLETES, TIME on 13th, TIME on 20th
I have tried some methods such as UNION ALL for example
Advertisement
Answer
You can get the previous time using a correlated subquery:
SELECT t.*, (SELECT t2.time FROM TableName t2 WHERE t2.runner = t.runner AND t2.eventId = t.eventId AND t2.date < t.date ORDER BY t2.date DESC LIMIT 1 ) prev_time FROM `TableName` t WHERE t.date = '2017-05-20';
For performance, you want an index on (runner, eventid, date, time)
.