History of world records

from the Artful Common Queries page

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
) ;
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 | 2025-01-01 |   58 | WR   |    6 | 2024-04-01 | WR   |
|  8 | John | 2025-03-01 |   55 | WR   |    6 | 2024-04-01 | WR   |
|  1 | Bill | 2025-01-01 |   58 | WR   |    8 | 2025-03-01 | WR   |
|  1 | Bill | 2025-01-01 |   58 | WR   |    9 | 2024-05-01 | WR   |
|  6 | John | 2024-04-01 |   54 | WR   |    9 | 2024-05-01 | WR   |
|  8 | John | 2025-03-01 |   55 | WR   |    9 | 2024-05-01 | WR   |
|  9 | Mark | 2024-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 | 2025-01-01 |   58 | WR   |   60 |
|  8 | John | 2025-03-01 |   55 | WR   |   31 |
|  6 | John | 2024-04-01 |   54 | WR   |   30 |
|  9 | Mark | 2024-05-01 |   51 | WR   |  175 |
+----+------+------------+------+------+------+
... and that's our result.

Last updated 23 Oct 2024