Skip to content
Advertisement

SQL query Compare two WHERE clauses using same table

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).

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement