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
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14 ;

INSERT INTO results (ID, Name, Date, Time, Note) VALUES
(1, 'Bill', '2012-01-01', 58, 'WR'),(2, 'John', '2012-01-01', 59, ''),
(3, 'Mark', '2012-01-01', 60, ''),(4, 'Bill', '2012-02-01', 59, ''),
(5, 'Bill', '2012-04-01', 61, ''),(6, 'John', '2012-04-01', 54, 'WR'),
(7, 'Mark', '2012-04-01', 57, ''),(8, 'John', '2012-03-01', 55, 'WR'),
(9, 'Mark', '2012-05-01', 51, 'WR'),(10, 'Bill', '2012-06-01', 56, ''),
(11, 'Mark', '2012-08-01', 53, ''),(12, 'Mark', '2012-07-01', 52, ''),
(13, 'John', '2012-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.

Return to the Artful Common Queries page