In a table of track event results, rows marked as 'WR' in the `Note` column represent world record times. How would we retrieve the history of those world records?
drop table if exists results;
CREATE TABLE results (
ID int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
Name varchar(50) COLLATE utf8_unicode_ci NOT NULL,
Date date NOT NULL,
Time int NOT NULL,
Note varchar(50) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14 ;
INSERT INTO results (ID, Name, Date, Time, Note) VALUES
(1, 'Bill', '2025-01-01', 58, 'WR'),(2, 'John', '2025-01-01', 59, ''),
(3, 'Mark', '2025-01-01', 60, ''),(4, 'Bill', '2025-02-01', 59, ''),
(5, 'Bill', '2024-04-01', 61, ''),(6, 'John', '2024-04-01', 54, 'WR'),
(7, 'Mark', '2024-04-01', 57, ''),(8, 'John', '2025-03-01', 55, 'WR'),
(9, 'Mark', '2024-05-01', 51, 'WR'),(10, 'Bill', '2024-06-01', 56, ''),
(11, 'Mark', '2024-08-01', 53, ''),(12, 'Mark', '2024-07-01', 52, ''),
(13, 'John', '2024-06-01', 53, '');
The solution is a two-step.
1. A world record has a beginning date, and an end date if it's been supplanted by a new record. That's a theta join:
SELECT a.*, b.id, b.date, b.note
FROM results a
LEFT JOIN results b ON a.note = b.note AND a.date < b.date
WHERE a.note = 'WR';
+----+------+------------+------+------+------+------------+------+
| ID | Name | Date | Time | Note | id | date | note |
+----+------+------------+------+------+------+------------+------+
| 1 | Bill | 2012-01-01 | 58 | WR | 6 | 2012-04-01 | WR |
| 8 | John | 2012-03-01 | 55 | WR | 6 | 2012-04-01 | WR |
| 1 | Bill | 2012-01-01 | 58 | WR | 8 | 2012-03-01 | WR |
| 1 | Bill | 2012-01-01 | 58 | WR | 9 | 2012-05-01 | WR |
| 6 | John | 2012-04-01 | 54 | WR | 9 | 2012-05-01 | WR |
| 8 | John | 2012-03-01 | 55 | WR | 9 | 2012-05-01 | WR |
| 9 | Mark | 2012-05-01 | 51 | WR | NULL | NULL | NULL |
+----+------+------------+------+------+------+------------+------+
2. From this set of result pairs, we want the pairs that occurred in sequence, plus the pair without an end—the current record holder. Those are the pairs with a minimum or null date difference ...
SELECT a.*, Min( DateDiff( IfNull( b.date, CurDate() ), a.date ) ) as Days
FROM results a
LEFT JOIN results b ON a.note = b.note AND a.date < b.date
WHERE a.note = 'WR'
GROUP BY id
ORDER BY Date;
+----+------+------------+------+------+------+
| ID | Name | Date | Time | Note | Days |
+----+------+------------+------+------+------+
| 1 | Bill | 2012-01-01 | 58 | WR | 60 |
| 8 | John | 2012-03-01 | 55 | WR | 31 |
| 6 | John | 2012-04-01 | 54 | WR | 30 |
| 9 | Mark | 2012-05-01 | 51 | WR | 175 |
+----+------+------------+------+------+------+
... and that's our result. |
|