Finding gaps between the values of a column in MySQL

Question

At a table of movie actors,

CREATE TABLE ArtistMap
(
ArtistID int(11) unsigned NOT NULL,
MovieID int(11) unsigned NOT NULL,
Year year,
INDEX(MovieID),
INDEX(Year),
PRIMARY KEY(ArtistID,MovieID)
) ENGINE=InnoDB

How can I select movies of an artist before a gap of X year in their works?

For example,

ArtistID    MovieID    Year
1           1          1985
1           2          1987
1           3          2000
1           4          2001
1           5          2002

I want to get the fields before a gap of 10 years in the artist's works (the gap occurs between 1987-200 when the artist did not play any movie). I want to get

ArtistID    MovieID    Year
1           1          1985
1           2          1987

If there is more than one gap, the first one suffices.

Answer 1

Use LEAD(year) or LAG(year) to discover a gap of 10+ years. Then use ROW_NUMBER() to keep rows before that gap. (See "windowing functions", available since MariaDB 10.2 or MySQL 8.0.)

Answer 2

(Not an answer, but a tip that might be useful.)

In MariaDB,

SELECT  ...
    FROM seq_1985_to_2022 AS seq
    LEFT JOIN ArtistMap AS am  ON am.Year = seq.num

will provide all the years in that range. Use seq.num for the "year"; am.Year will be NULL for the missing years. Therefore, you can fetch just the missing years by adding

WHERE am.Year IS NULL

Or maybe you want counts or blanks for the missing years. If so, use COALESCE(...) to turn NULL into 0 or blank.

Gap of 10 years -- Check the "gaps-and-islands" tag I added; it may help.